MySQL SQL查询显示最近的日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6186962/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query to show nearest date?
提问by Or Weinberger
I'm trying to figure out how to write a MySQL query that will return the closest 3 events in terms of date.
我想弄清楚如何编写一个 MySQL 查询，该查询将返回最接近日期的 3 个事件。
This is my table:
这是我的表：
EVENT_ID EVENT_NAME EVENT_START_DATE(DATETIME)
1 test 2011-06-01 23:00:00
2 test2 2011-06-03 23:00:00
3 test3 2011-07-01 23:00:00
4 test4 2011-08-09 23:00:00
5 test5 2011-06-02 23:00:00
6 test6 2011-04-20 23:00:00
So the query result should be for ID's 1,2,5 as they are the closest to occur in comparison to the current date..
所以查询结果应该是 ID 的 1,2,5，因为它们与当前日期相比最接近。
EDIT:query should find only future events.
编辑：查询应该只找到未来的事件。
回答by Mat
SELECT event_id
FROM Table
ORDER BY ABS( DATEDIFF( EVENT_START_DATE, NOW() ) )
LIMIT 3
The ABS()means that an event 1 day ago is just as close as an event 1 day in the future. If you only want events that haven't happened yet, do
这ABS()意味着 1 天前的事件与未来 1 天的事件一样接近。如果您只想要尚未发生的事件，请执行
SELECT event_id
FROM Table
WHERE EVENT_START_DATE > NOW()
ORDER BY EVENT_START_DATE
LIMIT 3
回答by hsz
SELECT *
FROM table
WHERE EVENT_START_DATE >= NOW()
ORDER BY EVENT_START_DATE
LIMIT 3
回答by Damjan Pavlica
The query from accepted answer actually just sort previously selected values, not filter them before select. But this query works for me:
来自接受答案的查询实际上只是对先前选择的值进行排序，而不是在选择之前对其进行过滤。但是这个查询对我有用：
SELECT event_id, event_date
FROM events
WHERE ABS(TIMESTAMPDIFF(DAY, event_date, $some_date)) < 10
ORDER BY event_date
Explanation: number 10 is a day range (both after and before). Without ABS()you can select only previous or future events, but I needed the closest.
说明：数字 10 是一天范围（之后和之前）。没有ABS()你只能选择以前或未来的事件，但我需要最接近的。
回答by Andre Backlund
I suppose this is what you'd be looking for. It's similar to everyone elses responses aswell.
我想这就是你要找的。其他人的回答也一样。
SELECT EVENT_ID FROM TABLE WHERE EVENT_START_DATE > NOW() ORDER BY ABS(DATEDIFF(EVENT_START_DATE, NOW())) ASC LIMIT 3
回答by xecaps12
SELECT event_id FROM Table ORDER BY EVENT_START_DATE LIMIT 3
