我有一个表格tblSchedule,以下是其中的值。
INSERT INTO tblSchedule (`scheduleID`, `eventID`, `price`, `eventDate`) VALUES
(1, 1, 150, '2013-04-20 00:00:00'),
(2, 1, 100, '2013-04-15 00:00:00'),
(3, 2, 80, '2013-04-18 00:00:00'),
(4, 3, 120, '2013-04-26 00:00:00'),
(5, 3, 140, '2013-04-22 00:00:00'),
(6, 2, 100, '2013-04-22 00:00:00');
我想获取按事件日期 ASC 排序的唯一事件
我尝试了以下查询,但这给出了错误的价格值和正确的事件日期。
SELECT MIN(eventDate) as minEventDate, eventId, price
FROM tblSchedule
GROUP BY eventID
ORDER BY minEventDate
单击此处查看实际操作的代码
谁能告诉我这里发生了什么以及正确查询的细分?
首先,您应该找到每个eventID
的MIN(EventDate)
,然后使用 JOIN 从原始表中查找具有这些eventID
和MIN(EventDate)
的行
SELECT * FROM tblSchedule
JOIN
( SELECT MIN(eventDate) as minEventDate,
eventId
FROM tblSchedule
GROUP BY eventID
) as T on tblSchedule.eventID=t.EventId
AND
tblSchedule.eventDate=t.minEventDate
ORDER BY eventDate
SQLFiddle demo
试试这个:
SELECT eventDate, eventId, price
FROM (SELECT eventDate, eventId, price
FROM tblSchedule
ORDER BY eventId, eventDate
) AS A
GROUP BY eventId;
查看 SQL 小提琴演示
输出
| EVENTDATE | EVENTID | PRICE |
|------------------------------|---------|-------|
| April, 15 2013 00:00:00+0000 | 1 | 100 |
| April, 18 2013 00:00:00+0000 | 2 | 80 |
| April, 22 2013 00:00:00+0000 | 3 | 140 |
你可以为此使用时间戳
SELECT * FROM `tblschedule` ORDER BY TIMESTAMP( eventDate )
或
SELECT DISTINCT(eventID) as event, eventDate FROM `tblschedule` ORDER BY TIMESTAMP( eventDate )
您可以放置要返回的任何列,而不是 *。
SQL 小提琴演示