我想从MySQL数据库中获取一天中每小时的所有最后一行,该表如下所示,
Id Name Vcum Date Time ------------------------------------------
通过使用sql可以得到以下结果吗?
1 A 5.5 2014-11-20 09:00
2 B 4.3 2014-11-20 09:05
3 A 6.7 2014-11-20 09:10
4 C 5.9 2014-11-20 09:10
5 A 7.2 2014-11-20 09:20
6 C 6.5 2014-11-20 09:25
7 B 8.1 2014-11-20 09:30
8 C 9.4 2014-11-20 09:35
9 A 6.5 2014-11-20 10:05
10 A 8.1 2014-11-20 10:28
11 C 9.5 2014-11-20 10:32
12 B 9.7 2014-11-20 10:36
13 A 8.8 2014-11-20 10:39
14 C 9.8 2014-11-20 10:42
15 B 9.9 2014-11-20 10:42
Id Name Vcum Date Time
------------------------------------------
5 A 7.2 2014-11-20 09:20 <-- Last row of HOUR(Time) = 9 for Name = A
7 B 8.1 2014-11-20 09:30 <-- Last row of HOUR(Time) = 9 for Name = B
8 C 9.4 2014-11-20 09:35 <-- Last row of HOUR(Time) = 9 for Name = C
13 A 8.8 2014-11-20 10:39 <-- Last row of HOUR(Time) =10 for Name = A
15 B 9.9 2014-11-20 10:42 <-- Last row of HOUR(Time) =10 for Name = B
14 C 9.8 2014-11-20 10:42 <-- Last row of HOUR(Time) =10 for Name = C
试试这个:
SELECT a.Id, a.Name, a.Vcum, a.Date, a.Time
FROM tableA a
INNER JOIN (SELECT a.Name, a.Date, MAX(a.Time) colTIme
FROM tableA a
GROUP BY a.Name, a.Date
) b ON a.Name = b.Name AND a.Date = b.Date AND a.Time = b.colTime
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句