SQL查询显示最近的日期？ [英] SQL Query to show nearest date?

问题描述

这是我的表：

我正在试图找出如何编写一个MySQL查询，将返回最近的3个事件的日期。 p>

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

所以查询结果应为ID为1,2,5，因为它们与当前日期相比最接近。

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..

编辑：查询只能找到未来的事件。

query should find only future events.

推荐答案

SELECT event_id 
FROM Table 
ORDER BY ABS( DATEDIFF( EVENT_START_DATE, NOW() ) ) 
LIMIT 3

ABS（）表示1天前的事件与未来1天的事件相同。如果您只希望尚未发生的事件，请执行

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

SELECT event_id 
FROM Table 
WHERE EVENT_START_DATE > NOW() 
ORDER BY EVENT_START_DATE 
LIMIT 3 

这篇关于SQL查询显示最近的日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！