SQL 查询问题。。
create table test1
(
id int ,
name varchar(20),
parentid int,
createtime datetime
)
insert into test1 select 1,'aaa',0,'2014-05-02 17:03'
insert into test1 select 2,'bbb',0,'2014-05-02 10:05'
insert into test1 select 3,'ccc',0,'2014-05-02 18:03'
insert into test1 select 4,'ddd',0,'2014-05-02 15:03'
insert into test1 select 5,'eee',2,'2014-05-02 16:03'
insert into test1 select 6,'fff',2,'2014-05-02 20:03'
insert into test1 select 7,'ggg',0,'2014-05-02 13:03'
insert into test1 select 8,'hhh',0,'2014-05-02 14:03'
select * from test1
/* 得到结果*/
id name parentid createtime
6 fff 2 2014-05-02 20:03
3 ccc 0 2014-05-02 18:03
1 aaa 0 2014-05-02 17:03
.......
/* 依次按createtime从高到低排序 ,并且过滤parentid 相同的数据
drop table test1
------解决方案--------------------是这个结果吗:
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
------解决方案--------------------
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
*/