需求描述

有两张表，想要查询每个班级排名第三的学生。排名按照总成绩排（数学+英语）。表结构如图：

student表：

score表：

mysql version 5.5 or 8.0.

希望大家帮我写两个版本的。谢谢大家了

---

数据：

student表：

1 name1 class1

2 name2 class1

3 name3 class1

4 name4 class1

5 name5 class2

6 name6 class2

7 name7 class2

8 name8 class2

score表：

1 1 English 100

2 1 Math 95

3 2 English 96

4 2 Math 95

5 3 English 100

6 3 Math 99

7 4 English 98

8 4 Math 97

9 5 English 99

10 5 Math 95

11 6 English 96

12 6 Math 94

13 7 English 92

14 7 Math 100

15 8 English 97

16 8 Math 95

解决方法

-- #1 建表语句及初始化脚本 CREATE TABLE stu (id SMALLINT, name varchar(12), className varchar(12) )CREATE TABLE score (id SMALLINT, stu_id varchar(12), courseName varchar(12), courseScore SMALLINT )INSERT INTO stu VALUES ('1','name1','class1'); INSERT INTO stu VALUES ('2','name2','class1'); INSERT INTO stu VALUES ('3','name3','class1'); INSERT INTO stu VALUES ('4','name4','class1'); INSERT INTO stu VALUES ('5','name5','class2'); INSERT INTO stu VALUES ('6','name6','class2'); INSERT INTO stu VALUES ('7','name7','class2'); INSERT INTO stu VALUES ('8','name8','class2');INSERT INTO score VALUES ('1','1','English','100'); INSERT INTO score VALUES ('2','1','Math','95'); INSERT INTO score VALUES ('3','2','English','96'); INSERT INTO score VALUES ('4','2','Math','95'); INSERT INTO score VALUES ('5','3','English','100'); INSERT INTO score VALUES ('6','3','Math','99'); INSERT INTO score VALUES ('7','4','English','98'); INSERT INTO score VALUES ('8','4','Math','97'); INSERT INTO score VALUES ('9','5','English','99'); INSERT INTO score VALUES ('10','5','Math','95'); INSERT INTO score VALUES ('11','6','English','96'); INSERT INTO score VALUES ('12','6','Math','94'); INSERT INTO score VALUES ('13','7','English','92'); INSERT INTO score VALUES ('14','7','Math','100'); INSERT INTO score VALUES ('15','8','English','97'); INSERT INTO score VALUES ('16','8','Math','95');-- #2 Mysql 8.0 SELECT * FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY className ORDER BY total DESC) rnFROM(SELECT B.id,B.className,SUM(courseScore) total FROM score AJOIN stu BON B.id = A.stu_idWHERE A.courseName IN('Math','English')GROUP BY B.idORDER BY B.className,total DESC)A )B WHERE rn<=3-- #3 Mysql 5.5 SELECT * FROM (select id,className,total,rank from (select className,heyf_tmp.id,heyf_tmp.total,@rownum := @rownum+1 ,if(@pdept= heyf_tmp.className,@rank:=@rank +1, @rank:= 1) as rank,@pdept:=heyf_tmp.classNamefrom ( SELECT B.id,B.className,SUM(courseScore) total FROM score AJOIN stu BON B.id = A.stu_idWHERE A.courseName IN('Math','English')GROUP BY B.idORDER BY B.className,total DESC) heyf_tmp ,(select @rownum:=0 , @pdept:= null ,@rank:= 0) aorder by className asc ,total desc) result ) A WHERE A.rank<=3 order by className,rank;-- #4 结果 /* id className total rank 3 class1 199 1 1 class1 195 2 4 class1 195 3 5 class2 194 1 7 class2 192 2 8 class2 192 3*/

执行结果

总结

以上是生活随笔为你收集整理的查询每个班级排名第三的学生的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。