34,590
社区成员
发帖
与我相关
我的任务
分享
select a.* from test1 a inner join (
select parentid,max(createtime) maxcreatetime from test1 where parentid=2 group by parentid
) b
on a.parentid=b.parentid and a.createtime=b.maxcreatetime
union all
select * from test1 where parentid=0
id name parentid createtime
6 fff 2 2014-05-02 20:03:00.000
1 aaa 0 2014-05-02 17:03:00.000
2 bbb 0 2014-05-02 10:05:00.000
3 ccc 0 2014-05-02 18:03:00.000
4 ddd 0 2014-05-02 15:03:00.000
7 ggg 0 2014-05-02 13:03:00.000
8 hhh 0 2014-05-02 14:03:00.000
SELECT A.*
FROM test1 A
WHERE NOT EXISTS ( SELECT 1
FROM test1 B
WHERE A.parentid = B.parentid
AND A.createtime < b.CREATEtime )
/*
id name parentid createtime
3 ccc 0 2014-05-02 18:03:00.000
6 fff 2 2014-05-02 20:03:00.000
*/
select a.* from test1 a inner join (
select parentid,max(createtime) maxcreatetime from test1 group by parentid) b
on a.parentid=b.parentid and a.createtime=b.maxcreatetime
id name parentid createtime
6 fff 2 2014-05-02 20:03:00.000
3 ccc 0 2014-05-02 18:03:00.000