按类型(SORT)分组,取每个组时间最往后的一条记录 例:SORT ACTION_ID ACTION_TIME
1 1 2007-1-18 14:52:30
2 2 2008-8-18 14:52:30
1 3 2008-8-18 14:52:30
2 4 2007-9-18 14:52:30结果应该是SORT ACTION_ID ACTION_TIME
1 3 2008-8-18 14:52:30
2 2 2008-8-18 14:52:30
1 1 2007-1-18 14:52:30
2 2 2008-8-18 14:52:30
1 3 2008-8-18 14:52:30
2 4 2007-9-18 14:52:30结果应该是SORT ACTION_ID ACTION_TIME
1 3 2008-8-18 14:52:30
2 2 2008-8-18 14:52:30
SELECT * FROM TABLE_NAME WHERE (SORT,ACTION_TIME) IN (SELECT SORT,MAX(ACTION_TIME) FROM TABLE_NAME GROUP BY SORT)
from a,
(
select sort,max(action_time) action_time
from a
group by sort
) b
where a.sort=b.sort and a.action_time=b.action_time和楼上的语句结果一样的
SELECT SORT,MAX(ACTION_TIME) ACTION_TIME FROM T GROUP BY SORT
) TT
WHERE TT.SORT = T.SORT AND TT.ACTION_TIME = T.ACTION_TIME;这条语句在 ORACEL 和 SQL SERVER 等数据库中都可执行通过的。