要求按userid求出表tb时间最大的记录,如果时间相同则求出id最大的记录
结果: 3 张三 2008-7-4 fwe442231
5 李斯 2008-7-4 fffff表tb
------------
id userid operdate text
1 张三 2008-7-3 few
2 张三 2008-7-4 fg3434
3 张三 2008-7-4 fwe442231
4 李斯 2008-7-3 vvv
5 李斯 2008-7-4 fffff
结果: 3 张三 2008-7-4 fwe442231
5 李斯 2008-7-4 fffff表tb
------------
id userid operdate text
1 张三 2008-7-3 few
2 张三 2008-7-4 fg3434
3 张三 2008-7-4 fwe442231
4 李斯 2008-7-3 vvv
5 李斯 2008-7-4 fffff
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb (id int,userid varchar(4),operdate datetime,text varchar(9))
insert into #tb
select 1,'张三','2008-7-3','few' union all
select 2,'张三','2008-7-4','fg3434' union all
select 3,'张三','2008-7-4','fwe442231' union all
select 4,'李斯','2008-7-3','vvv' union all
select 5,'李斯','2008-7-4','fffff'select * from #tb as a
where id in ( select top 1 id from #tb where userid=a.userid order by operdate desc)/*
2 张三 2008-07-04 00:00:00.000 fg3434
5 李斯 2008-07-04 00:00:00.000 fffff
*/
and id in (select max(id) from tb group by userid)
FROM
(SELECT NAME,MAX(operdate)operdate,MAX(ID)ID
FROM TABLE1
GROUP BY NAME)A LEFT JOIN TABLE1 B ON (A.ID=B.ID AND A.NAME=B.NAME)
and id in (select max(id) from tb group by userid)select * from tb a where not exists(select 1 from tb where userid=a.userid and id>a.id)
都可以
where operdate in (select max(operdate) from ta a where id=a.id) and
id in (select max(id) from ta group by userid) order by id
其他语句如果第一条记录时间为2009就不对了。select * from #tb as a
where id in ( select top 1 id from #tb where userid=a.userid order by operdate desc,id desc)
还是不太明白~~
select * from #tb where operdate in(select max(operdate) from #tb group by userid)
and id in(select max(id) from #tb group by operdate)也对.如果第二个筛选为group by userid 就不符合题意了。