select * from test t1 where not exists(select 1 from test where ftime = t1.ftime and nn > t1.nn)
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( ftime datetime, pid varchar(10), nn int )insert into tb(ftime,pid,nn) values('2006-12-05','aa',12) insert into tb(ftime,pid,nn) values('2006-12-05','bb',11) insert into tb(ftime,pid,nn) values('2006-12-05','cc',12) insert into tb(ftime,pid,nn) values('2006-12-06','aa',9) insert into tb(ftime,pid,nn) values('2006-12-06','bb',25) insert into tb(ftime,pid,nn) values('2006-12-07','bb',32) insert into tb(ftime,pid,nn) values('2006-12-07','cc',26)select convert(varchar(10),a.ftime,120) as ftime , a.pid , a.nn from tb a, (select ftime , max(nn) as nn from tb group by ftime) b where a.ftime = b.ftime and a.nn = b.nn order by a.ftime , a.piddrop table tbftime pid nn ---------- ---------- ----------- 2006-12-05 aa 12 2006-12-05 cc 12 2006-12-06 bb 25 2006-12-07 bb 32(所影响的行数为 4 行)
where not exists(select 1 from test where ftime = t1.ftime and nn > t1.nn)
drop table tb
gocreate table tb
(
ftime datetime,
pid varchar(10),
nn int
)insert into tb(ftime,pid,nn) values('2006-12-05','aa',12)
insert into tb(ftime,pid,nn) values('2006-12-05','bb',11)
insert into tb(ftime,pid,nn) values('2006-12-05','cc',12)
insert into tb(ftime,pid,nn) values('2006-12-06','aa',9)
insert into tb(ftime,pid,nn) values('2006-12-06','bb',25)
insert into tb(ftime,pid,nn) values('2006-12-07','bb',32)
insert into tb(ftime,pid,nn) values('2006-12-07','cc',26)select convert(varchar(10),a.ftime,120) as ftime , a.pid , a.nn from tb a,
(select ftime , max(nn) as nn from tb group by ftime) b
where a.ftime = b.ftime and a.nn = b.nn
order by a.ftime , a.piddrop table tbftime pid nn
---------- ---------- -----------
2006-12-05 aa 12
2006-12-05 cc 12
2006-12-06 bb 25
2006-12-07 bb 32(所影响的行数为 4 行)