ID Name Scroe Time
001 A 90 2006-1-1
002 A 80 2006-10-1
003 B 95 2006-11-10
004 C 50 2006-12-1
005 B 40 2006-4-3
006 A 60 2006-5-7要求用一句sql语句查询,结果:
ID Name Scroe Time
002 A 80 2006-10-1
003 B 95 2006-11-10
004 C 50 2006-12-1结果Name必须是唯一的并且Time字段取最近的,切每个不同的Name都必须出现
001 A 90 2006-1-1
002 A 80 2006-10-1
003 B 95 2006-11-10
004 C 50 2006-12-1
005 B 40 2006-4-3
006 A 60 2006-5-7要求用一句sql语句查询,结果:
ID Name Scroe Time
002 A 80 2006-10-1
003 B 95 2006-11-10
004 C 50 2006-12-1结果Name必须是唯一的并且Time字段取最近的,切每个不同的Name都必须出现
insert into t select '001','A',90 ,'2006-1-1'
insert into t select '002', 'A' , 80 , '2006-10-1'
insert into t select '003', 'B' , 95 , '2006-11-10'
insert into t select '004', 'C' , 50 , '2006-12-1'
insert into t select '005', 'B' , 40 , '2006-4-3'
insert into t select '006', 'A', 60 , '2006-5-7'select * from t b where not exists(select 1 from t a where a.name=b.name and a.time>b.time)
drop table t
-----------------------------------------------
id name score time
002 A 80 2006-10-01 00:00:00.000
003 B 95 2006-11-10 00:00:00.000
004 C 50 2006-12-01 00:00:00.000LZ不厚道,不给点分~
我在后面加where name<>"C"
速度比较慢
我是在大数据量下测的
3W多条记录
where charindex(name,"C")>0