Question

sql 应用题

学生信息表中有三个字段，学生编号，课程编号，课程成绩。
问题：求出每门课程中的前两名学生的信息。
我用的是sql

Answer 1

CREATE TABLE dongdong
(
id INT IDENTITY(1,1) PRIMARY KEY,
uuid INT,
kid INT,
cj INT
)
GO

INSERT INTO dongdong(uuid,kid,cj) VALUES(1,1,95)
INSERT INTO dongdong(uuid,kid,cj) VALUES(2,2,75)
INSERT INTO dongdong(uuid,kid,cj) VALUES(3,1,85)
INSERT INTO dongdong(uuid,kid,cj) VALUES(4,1,96)
INSERT INTO dongdong(uuid,kid,cj) VALUES(5,2,80)
INSERT INTO dongdong(uuid,kid,cj) VALUES(1,2,74)
INSERT INTO dongdong(uuid,kid,cj) VALUES(2,3,74)

;
WITH tempTable1 AS (
SELECT * ,ROW_NUMBER() OVER (partition BY d.kid ORDER BY d.cj DESC) rowid
FROM dongdong d
), tempTable2 AS(
SELECT *,ROW_NUMBER() OVER (partition BY d.kid ORDER BY d.cj DESC) rowid
FROM dongdong d
)
SELECT * FROM tempTable2 WHERE rowid<=2

DROP TABLE dongdong

Answer 2

你用的是什么数据库啊？

那就随便给你写个吧：

select 学生编号, 课程编号, 课程成绩 from 学生信息表 limit 0, 2 group by 课程编号 order by 课程成绩 desc;

Answer 3

oracle下:

select 课程编号,学生编号,课程成绩
from
(
select 课程编号,学生编号,课程成绩,
row_number() over(partition by 课程编号 order by 课程成绩 desc) rn
from 学生信息表
)
where rn <= 2;