sql求每门课程的前两名

Question

IF EXISTS (SELECT * FROM sys.sysobjects WHERE name = 'course') 
DROP TABLE course
CREATE TABLE course 
(
sname VARCHAR(10),
scourse VARCHAR(10),
score  FLOAT
)INSERT INTO course VALUES('张三','数学',98);
INSERT INTO course VALUES('李四','数学',95);
INSERT INTO course VALUES('王五','数学',90);
INSERT INTO course VALUES('赵六','英语',100);
INSERT INTO course VALUES('张三','英语',99);
INSERT INTO course VALUES('李四','英语',69);
-------每门课的前两名
SELECT * FROM course

Answer 1

;with maco as
(select row_number() over (partition by scourse order by score desc) as id,
* from course)select * from maco where id<3
/*
id                   sname      scourse    score
-------------------- ---------- ---------- ----------------------
1                    张三         数学         98
2                    李四         数学         95
1                    赵六         英语         100
2                    张三         英语         99
*/

Answer 2

select sname,
       scourse,
       score
from course t
where sname in(select top 2 sname from course where scourse = t.scourse order by score desc)

Answer 3

select distinct B.sname,B.scourse,B.score
from course A
cross apply (select top 2 sname,scourse,score 
from course  
where  scourse =A.scourse
order by score desc) B

/*
sname      scourse    score
---------- ---------- ----------------------
李四         数学         95
张三         数学         98
张三         英语         99
赵六         英语         100(4 行受影响)