求每门功课成绩最好的前两名?

[hello926]

create table stu(
sno varchar(2),
cursorid varchar(2),
grade decimal(18,0))
insert stu
select 1,1,99 union all
select 2,1,98 union all
select 3,1,100 union all
select 4,2, 88 union all
select 5,2,87 union all
select 6,2,88 union all
select 7,3,99 union all
select 8,3,98 union all
select 9,3,100

[feixianxxx]

select sno,cursorid,grade
from 
(
   select rn=row_number() over(partition by cursorid order by grade desc )
   from stu
) k
where rn between 1 and 2

根据需求 你可以将上面的row_number 换成RANK() over()... 或者 dense_rank()....

[feixianxxx]

create table stu
(
sno varchar(2),
cursorid varchar(2),
grade decimal(18,0))
insert stu
select 1,1,99 union all
select 2,1,98 union all
select 3,1,100 union all
select 4,2, 88 union all
select 5,2,87 union all
select 6,2,88 union all
select 7,3,99 union all
select 8,3,98 union all
select 9,3,100
go
select cursorid,sno,grade
from 
(
   select rn=row_number() over(partition by cursorid order by grade desc ),*
   from stu
) k
where rn between 1 and 2
/*
cursorid sno  grade
-------- ---- ---------------------------------------
1        3    100
1        1    99
2        4    88
2        6    88
3        9    100
3        7    99
*/

[zhengduan964532]

IF object_id('stu')IS NOT NULL DROP TABLE stu
create table stu(
sno varchar(2),
cursorid varchar(2),
grade decimal(18,0))
insert stu
select 1,1,99 union all
select 2,1,98 union all
select 3,1,100 union all
select 4,2, 88 union all
select 5,2,87 union all
select 6,2,88 union all
select 7,3,99 union all
select 8,3,98 union all
select 9,3,100 


SELECT * FROM stu 


;WITH romance
AS 
(
SELECT sno,cursorid,grade,num=row_number()OVER (PARTITION   BY cursorid ORDER  BY grade desc )
FROM stu 
)
SELECT *  FROM romance 
WHERE num<=2

[zhengduan964532]

慢了，郁闷