SQL：查询每科目前三名学生及分数

一、创建得分表

-- 创建student_score表
CREATE TABLE student_score(
    name varchar(20),
    subject varchar(20),
    score int
);

二、插入数据

-- 插入数据

INSERT INTO student_score(name, subject, score)
VALUES ('张三', '语文', 98);

INSERT INTO student_score(name, subject, score)
VALUES ('张三', '数学', 80);

INSERT INTO student_score(name, subject, score)
VALUES ('张三', '英语', 90);

INSERT INTO student_score(name, subject, score)
VALUES ('李四', '语文', 88);

INSERT INTO score (name, subject, score)
VALUES ('李四', '数学', 86);

INSERT INTO student_score(name, subject, score)
VALUES ('李四', '英语', 88);

INSERT INTO student_score(name, subject, score)
VALUES ('李明', '语文', 60);

INSERT INTO student_score(name, subject, score)
VALUES ('李明', '数学', 86);

INSERT INTO student_score(name, subject, score)
VALUES ('李明', '英语', 88);

INSERT INTO student_score(name, subject, score)
VALUES ('林风', '语文', 74);

INSERT INTO student_score(name, subject, score)
VALUES ('林风', '数学', 99);

INSERT INTO student_score(name, subject, score)
VALUES ('林风', '英语', 59);

INSERT INTO student_score(name, subject, score)
VALUES ('严明', '英语', 96);

 

三、查询每科目前三名学生及分数

-- 查询每科目前三名学生及分数

SET @rank = 0;
SET @subjt = NULL;
SELECT rank, subject, name, score
FROM (
	SELECT subject, name, score
		, @rank := if(@subjt = subject, @rank + 1, 1) AS rank
		, @subjt := subject
	FROM student_score
	ORDER BY subject, score DESC 
) rank_score where rank <=3