select * from book a,(select max(time) as time,user from book group by user) b where a.time=b.time and a.user=b.user order by id desc
--测试环境 declare @t table(id int identity(1,1),booknam varchar(10),[user] varchar(10),time datetime) insert into @t select '书名1','张三','2005-12-1' union all select '书名2','张三','2005-12-2' union all select '书名3','张三','2005-12-3' union all select '书名4','李四','2005-12-4' union all select '书名5','李四','2005-12-5' union all select '书名6','张三','2005-12-6' union all select '书名7','王五','2005-12-7' union all select '书名8','王五','2005-12-8' union all select '书名9','王五','2005-12-9' --查询 select * from @t A where not exists (select 1 from @t where [user]=A.[user] and time>A. time) order by id desc --结果 id booknam user time ----------- ---------- ---------- ------------------------- 9 书名9 王五 2005-12-09 00:00:00.000 6 书名6 张三 2005-12-06 00:00:00.000 5 书名5 李四 2005-12-05 00:00:00.000(所影响的行数为 3 行)
select * from book where time in (select max(time)from book group by [user]) order by id desc
where a.time=b.time and a.user=b.user order by id desc
declare @t table(id int identity(1,1),booknam varchar(10),[user] varchar(10),time datetime)
insert into @t select '书名1','张三','2005-12-1'
union all select '书名2','张三','2005-12-2'
union all select '书名3','张三','2005-12-3'
union all select '书名4','李四','2005-12-4'
union all select '书名5','李四','2005-12-5'
union all select '书名6','张三','2005-12-6'
union all select '书名7','王五','2005-12-7'
union all select '书名8','王五','2005-12-8'
union all select '书名9','王五','2005-12-9'
--查询
select
*
from
@t A
where not exists
(select 1 from @t where [user]=A.[user] and time>A. time)
order by id desc
--结果
id booknam user time
----------- ---------- ---------- -------------------------
9 书名9 王五 2005-12-09 00:00:00.000
6 书名6 张三 2005-12-06 00:00:00.000
5 书名5 李四 2005-12-05 00:00:00.000(所影响的行数为 3 行)