有一表 auid uname datecreated price
-----------------------------------------------------
1 aa 2007-12-26 10:17:07.000 10
2 bb 2007-12-24 10:19:07.000 20
3 cc 2007-12-26 10:17:07.000 30
1 aa 2007-12-29 18:17:07.000 40
3 cc 2007-12-31 12:17:07.000 50
3 cc 2007-12-31 18:50:07.000 66要找出每个人最后一次记录(2007-12-31 23:59:59之前的每个人最后一次记录)请指教!!!!谢谢啦
-----------------------------------------------------
1 aa 2007-12-26 10:17:07.000 10
2 bb 2007-12-24 10:19:07.000 20
3 cc 2007-12-26 10:17:07.000 30
1 aa 2007-12-29 18:17:07.000 40
3 cc 2007-12-31 12:17:07.000 50
3 cc 2007-12-31 18:50:07.000 66要找出每个人最后一次记录(2007-12-31 23:59:59之前的每个人最后一次记录)请指教!!!!谢谢啦
insert into @tb select '1','aa','2007-12-26 10:17:07.000',10
insert into @tb select '2','bb','2007-12-24 10:19:07.000',20
insert into @tb select '3','cc','2007-12-26 10:17:07.000',30
insert into @tb select '1','aa','2007-12-29 18:17:07.000',40
insert into @tb select '3','cc','2007-12-31 12:17:07.000',50
insert into @tb select '3','cc','2007-12-31 18:50:07.000 ',60
select * from @tb a where
not exists(select 1 from @tb where uid=a.uid and datecreated>a.datecreated)
and datecreated<'2007-12-31 23:59:59'
order by uiduid uname datecreated price
1 aa 2007-12-29 18:17:07.000 40
2 bb 2007-12-24 10:19:07.000 20
3 cc 2007-12-31 18:50:07.000 60
from a aa
where not exists(select 1 from a where aa.uid = uid and [datecreated] > aa.datecreated)
insert into @tb select '1','aa','2007-12-26 10:17:07.000',10
insert into @tb select '2','bb','2007-12-24 10:19:07.000',20
insert into @tb select '3','cc','2007-12-26 10:17:07.000',30
insert into @tb select '1','aa','2007-12-29 18:17:07.000',40
insert into @tb select '3','cc','2007-12-31 12:17:07.000',50
insert into @tb select '3','cc','2007-12-31 18:50:07.000 ',60
select * from @tb a where
not exists(select 1 from @tb where uid=a.uid and datecreated>a.datecreated)
and datecreated<'2007-12-29 23:59:59'
order by uiduid uname datecreated price
1 aa 2007-12-29 18:17:07.000 40
2 bb 2007-12-24 10:19:07.000 20
----------- ---------- ----------------------- -----------
1 aa 2007-12-29 18:17:07.000 40
2 bb 2007-12-24 10:19:07.000 20
3 cc 2007-12-31 18:50:07.000 66(3 行受影响)
select * from tablename a where
not exists(select 1 from tablename
where uid=a.uid and datecreated>a.datecreated)
and datediff(day,datecreated,'2008-01-01')>0
order by uid