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 行受影响)