如何取每组最新的一条数据?

li_zhifu 2007-03-09 10:47:43
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
...全文
1098 点赞 收藏 24
写回复
24 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wyf2 2007-03-13
对了,问下
select * from tb a
where not exists(select * from tb where groupid=a.groupid and lastdatetime>a.lastdatetime)
lastdatetime>a.lastdatetime这个有什么区别,不懂
回复
cooljiji 2007-03-13
libin_ftsafe(子陌红尘:当libin告别ftsafe) ( ) 信誉:105 Blog 2007-3-9 10:48:58 得分: 10 第一个错了.为什么会给他分啊?
回复
wyf2 2007-03-13
我也来写一个:
select b.id,b.name,a.groupid,a.lastdatetime
from ( select groupid,max(lastdatetime)lastdatetime
from myexample group by groupid) a join myexample b on a.lastdatetime= b.lastdatetime
回复
select * from tablename a where a.id in (select top 1 b.id from tablename b where a.groupid=b.groupid order by lastdatetime desc) order by a.groupid
回复
dragon_sea 2007-03-12
上面是对的!顶!
回复
zyj1219304 2007-03-12
select * from tableName where id in(select max(lastdatetime) from tableName group by groupId);
回复
guichuan 2007-03-12
看错啦
回复
guichuan 2007-03-12
select * from TableName where id in (select max(ID) from TableName group by GroupId)
回复
sanyii31 2007-03-12
SELECT *
FROM 表
WHERE EXISTS(
SELECT *
FROM (
SELECT GroupId,max(LastDateTime) maxLastDateTime
FROM 表
GROUP BY GroupId
)maxt
WHERE 表.GroupId=maxt.GroupId
AND 表.LastDateTime=maxt.maxLastDateTime
)
回复
Sevence_Wiser 2007-03-12
select id from tabelname as ta,
(select GroupID,max(lastupdatedate) as lastupdatedate from tabelname group by GroupID) as tb
where ta.GroupID = tb.GroupID and ta.lastupdatedate = tb.lastupdatedate
回复
l932 2007-03-12
select * from 表名 where id = (select id from 表 where lastdatetime = (select max(lastdatetime) from 表 where groupid = 1))
回复
li_zhifu 2007-03-09
已搞定,原来Max(LastDateTime)也是有效的,汗
回复
shoulley 2007-03-09
select * from tb a
where not exists(select * from tb where groupid=a.groupid and lastdatetime>a.lastdatetime)
正解!!
回复
dawugui 2007-03-09
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 行)
回复
mengmou 2007-03-09
不写了,顶
回复
paoluo 2007-03-09
libin_ftsafe(子陌红尘:当libin告别ftsafe),你的寫法有問題的,id和lastdatetime沒有絕對的關係的。

應該改為

select t.* from @t t where t.lastdatetime=(select max(lastdatetime) from @t where groupid=t.groupid)

select t.* from @t t where not exists(select 1 from @t where groupid=t.groupid and lastdatetime>t.lastdatetime)

select a.* from @t a,(select groupid,max(lastdatetime) as lastdatetime from @t group by groupid) b where a.lastdatetime=b.lastdatetime and a.groupid=b.groupid
回复
paoluo 2007-03-09
也寫下


Select *
From TableName A
Where lastdatetime = (Select Max(lastdatetime) From TableName Where name = A.name)
回复
子陌红尘 2007-03-09
declare @t table(id int,name varchar(10),groupid varchar(4),astdatetime varchar(20))
insert into @t select 1,'aaa','001','2007-03-06'
insert into @t select 2,'bbb','001','2007-03-07'
insert into @t select 3,'ccc','001','2007-03-05'
insert into @t select 4,'ddd','002','2007-03-05'
insert into @t select 5,'eee','002','2007-03-08'


select t.* from @t t where t.id=(select max(id) from @t where groupid=t.groupid)

select t.* from @t t where not exists(select 1 from @t where groupid=t.groupid and id>t.id)

select a.* from @t a,(select groupid,max(id) as id from @t group by groupid) b where a.id=b.id and a.groupid=b.groupid
回复
wangdehao 2007-03-09
select * from tb a
inner join
(select groupid ,max(lastdatetime) lastdatetime from tb group by groupid )b
on a.groupid =b.groupid and a.lastdatetime=b.lastdatetime
回复
青锋-SS 2007-03-09
select tb1.* from tb1,(select groupid,max(lastdatetime) from tb1) t where tb1.groupid=t.groupid and tb1.lastdatetime=t.lastdatetime;
回复
相关推荐
发帖
应用实例
创建于2007-09-28

2.7w+

社区成员

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2007-03-09 10:47
社区公告
暂无公告