带日期时间的 MySQL Select 语句

Question

我有这个适用于我的 MySQL SELECT 语句，但我想稍微改进一下:

SELECT event.id_event, event_title, event_details, event_date_time, event_showtime.id_show, event_category.id_category, ( 6371 * ACOS( COS( RADIANS(  '49.20513921227407' ) ) * COS( RADIANS( event_showtime.latitude ) ) * COS( RADIANS( event_showtime.longitude ) - RADIANS(  '18.762441839599678' ) ) + SIN( RADIANS(  '49.20513921227407' ) ) * SIN( RADIANS( event_showtime.latitude ) ) ) ) AS distance
FROM event
JOIN event_showtime ON event.id_event = event_showtime.id_event
JOIN event_category ON event.id_category = event_category.id_category
HAVING distance <  '5'
ORDER BY distance
LIMIT 0 , 20

这将返回一组 3 行:

id_event    event_title event_details   event_date_time id_show id_category distance
1   Testovacia na FRI   Testovacia udalosť na FRI   2014-04-01 20:00:00 2   1   0.390327906350887
1   Testovacia na FRI   Testovacia udalosť na FRI   2014-03-31 16:13:17 1   1   0.390327917895938
2   Čin-Čin Čin-Čin je nádherný poetický príbeh o dospievaní. ...   2014-03-31 15:00:00 3   2   1.02364018489261

这基本上是位置上的一组事件显示时间。但诀窍在于结果，我只想看到日期时间最接近 NOW() 的 id = 1 事件的一个结果。这可能吗？

编辑:这是 event_showtime 表的内容:

INSERT INTO `event_showtime` (`id_show`, `id_event`, `latitude`, `longitude`, `event_date_time`) VALUES
(1, 1, 49.2016762922894, 18.7615620750428, '2014-03-31 16:13:17'),
(2, 1, 49.2016762922894, 18.7615620750428, '2014-04-01 20:00:00'),
(3, 2, 49.2113914818564, 18.7520992416382, '2014-03-31 15:00:00'),
(4, 2, 49.0545135142313, 20.2952223676682, '2014-04-16 11:00:00'),
(5, 2, 49.2113914818564, 18.7520992416382, '2014-04-23 11:00:00'),
(6, 2, 49.0545135142313, 20.2952223676682, '2014-04-30 11:00:00'),
(7, 2, 49.2016762922894, 18.7615620750428, '2014-04-29 12:00:00'),
(8, 1, 49.2016762922894, 18.7615620750428, '2014-04-24 12:00:00');

Answer 1

试试这个:

SELECT id_event , event_title, event_details, event_date_time, id_show, id_category,  distance FROM(
SELECT event.id_event id_event, event_title, event_details, max(event_date_time) event_date_time, max(event_showtime.id_show) id_show, event_category.id_category id_category, ( 6371 * ACOS( COS( RADIANS(  '49.20513921227407' ) ) * COS( RADIANS( event_showtime.latitude ) ) * COS( RADIANS( event_showtime.longitude ) - RADIANS(  '18.762441839599678' ) ) + SIN( RADIANS(  '49.20513921227407' ) ) * SIN( RADIANS( event_showtime.latitude ) ) ) ) AS distance
FROM event
JOIN event_showtime ON event.id_event = event_showtime.id_event
JOIN event_category ON event.id_category = event_category.id_category
GROUP BY event.id_event
HAVING distance <  '5'
ORDER BY distance
LIMIT 0 , 20
  )t