日期:2014-05-17 浏览次数:20502 次
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [班级] varchar(100), [分数] INT);
insert #temp
select 'A','30' union all
select 'A','50' union all
select 'C','52' union all
select 'D','30' union all
select 'A','84' union all
select 'E','68' union all
select 'A','70' union all
select 'B','32'
--SQL:
;WITH CTE AS
(
SELECT ROWID=ROW_NUMBER() OVER(PARTITION BY [班级] ORDER BY 分数 DESC),*
from #temp
)
SELECT [班级],分数 FROM CTE
WHERE ROWID <= 2
/*
班级 分数
A 84
A 70
B 32
C 52
D 30
E 68
*/