需求描述

有兩張表，想要查詢每個(gè)班級(jí)排名第三的學(xué)生。排名按照總成績(jī)排（數(shù)學(xué)+英語）。表結(jié)構(gòu)如圖：

student表：

score表：

mysql version 5.5 or 8.0.

希望大家?guī)臀覍憙蓚€(gè)版本的。謝謝大家了

---

數(shù)據(jù)：

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 結(jié)果 /* 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*/

執(zhí)行結(jié)果

總結(jié)

以上是生活随笔為你收集整理的查询每个班级排名第三的学生的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。