如何取每组最新的一条数据？
id name groupid lastdatetime
1 aaa 001 2007-03-06
2 bbb 001 2007-03-07
3 ccc 001 2007-03-05
4 ddd 002 2007-03-05
5 eee 002 2007-03-08
--------------------------------------------
想得到这样的结果
id name groupid lastdatetime
2 bbb 001 2007-03-07
5 eee 002 2007-03-08
[解决办法]
select t.* from 表 t where t.id=(select max(id) from 表 where name=t.name)
[解决办法]
select * from tb a
where not exists(select * from tb where groupid=a.groupid and lastdatetime> a.lastdatetime)
[解决办法]
select tb1.* from tb1,(select groupid,max(lastdatetime) from tb1) t where tb1.groupid=t.groupid and tb1.lastdatetime=t.lastdatetime;
[解决办法]
也寫下
Select *
From TableName A
Where lastdatetime = (Select Max(lastdatetime) From TableName Where name = A.name)
[解决办法]
不写了，顶
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
id int,
name varchar(10),
groupid varchar(10),
lastdatetime datetime
)
insert into tb(id,name,groupid,lastdatetime) values(1, 'aaa ', '001 ', '2007-03-06 ')
insert into tb(id,name,groupid,lastdatetime) values(2, 'bbb ', '001 ', '2007-03-07 ')
insert into tb(id,name,groupid,lastdatetime) values(3, 'ccc ', '001 ', '2007-03-05 ')
insert into tb(id,name,groupid,lastdatetime) values(4, 'ddd ', '002 ', '2007-03-05 ')
insert into tb(id,name,groupid,lastdatetime) values(5, 'eee ', '002 ', '2007-03-08 ')
select a.* from tb a,
(select groupid , max(lastdatetime) as lastdatetime from tb group by groupid) b
where a.groupid = b.groupid and a.lastdatetime = b.lastdatetime
drop table tb
id name groupid lastdatetime
----------- ---------- ---------- ------------------------------------------------------
5 eee 002 2007-03-08 00:00:00.000
2 bbb 001 2007-03-07 00:00:00.000
（所影响的行数为 2 行）
[解决办法]
select * from tb a
where not exists(select * from tb where groupid=a.groupid and lastdatetime> a.lastdatetime)
正解!!
