假数据
id code datatime
1 a 2008-09-1 20:20:20
2 a 2008-09-1 20:20:21
3 b 2008-09-2 20:20:22
4 b 2008-09-2 20:20:23
5 c 2008-09-3 20:20:24
6 c 2008-09-3 20:20:25
······datetime 为时间类型问题:以code字段分组,取每组中最大时间的那“整条记录”!
我用的PL-SQL 。
id code datatime
1 a 2008-09-1 20:20:20
2 a 2008-09-1 20:20:21
3 b 2008-09-2 20:20:22
4 b 2008-09-2 20:20:23
5 c 2008-09-3 20:20:24
6 c 2008-09-3 20:20:25
······datetime 为时间类型问题:以code字段分组,取每组中最大时间的那“整条记录”!
我用的PL-SQL 。
from table a
,(select code,max(datatime)
from table
group by code
) b
where a.code=b.code
and a.datatime=b.datatime
from table a
,(select code,max(datatime) as datetime
from table
group by code
) b
where a.code=b.code
and a.datatime=b.datatime
SELECT ID, CODE, DATATIME
FROM (SELECT TABLE1.* ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY DATATIME DESC) RN
FROM TABLE1)
WHERE RN = 1;
max
和row_number
数据量大时,MAX会略快点