sql求每门课程的前两名,该如何解决

sql求每门课程的前两名
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

[解决办法]

SQL code

;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                    张三         数学         982                    李四         数学         951                    赵六         英语         1002                    张三         英语         99*/
[解决办法]
SQL code
select sname,       scourse,       scorefrom course twhere sname in(select top 2 sname from course where scourse = t.scourse order by score desc)
[解决办法]
SQL code
select distinct B.sname,B.scourse,B.scorefrom course Across 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 行受影响)