有表 A 如下,id name time
001 黄 2006-11-10
002 李 2005-11-10
003 黄 2006-9-15
004 黄 2004-12-13
005 李 2006-8-17
006 李 2006-10-16
007 黄 2002-11-14在下要选出的结果是
id name time
001 黄 2006-11-10
006 李 2006-10-16
(注:我的意思是要按Name来分组,选出time为每组中最大的记录)
001 黄 2006-11-10
002 李 2005-11-10
003 黄 2006-9-15
004 黄 2004-12-13
005 李 2006-8-17
006 李 2006-10-16
007 黄 2002-11-14在下要选出的结果是
id name time
001 黄 2006-11-10
006 李 2006-10-16
(注:我的意思是要按Name来分组,选出time为每组中最大的记录)
from 表名 T
where not exists (select * from 表名 where name=T.name and time>t.time)
from 表名 T
where not exists (select * from 表名 where name=T.name and [time]>t.[time])
INSERT INTO @tb
SELECT 001, '黄', '2006-11-10'
UNION ALL SELECT 002, '李', '2005-11-10'
UNION ALL SELECT 003, '黄', '2006-9-15'
UNION ALL SELECT 004, '黄', '2004-12-13'
UNION ALL SELECT 005, '李', '2006-8-17'
UNION ALL SELECT 006, '李', '2006-10-16'
UNION ALL SELECT 007, '黄', '2002-11-14'SELECT * FROM @tb A WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE name = A.name AND time > A.time)id name time
----------- ----- ------------------------------------------------------
1 黄 2006-11-10 00:00:00.000
6 李 2006-10-16 00:00:00.000
(select name,max(time) as time from tb group by name) b
where a.name = b.name and a.time = b.time
union all select '002', '李', '2005-11-10'
union all select '003', '黄', '2006-9-15'
union all select '004', '黄', '2004-12-13'
union all select '005', '李', '2006-8-17'
union all select '006', '李', '2006-10-16'
union all select '007', '黄', '2002-11-14'select A.id,A.Name,A.time from @A A,
(select name,max(time) as time from @A group by name) B where A.name = B.name and A.time = B.time
order by id