mysql--＞学生课程成绩，取课程的成绩都排在前五

题目来源：群友求解，特此梳理

题目需求：
现已将上表查询的结果(第一题结果) 保存为新表new_score，根据 new_score 表查询出满足以下条件的结果:
1、语数英三门课程的成绩都排在前五 (包含第五);
2、条件1中为单科成绩排名，从高到低排列，排名连续，成绩相同时采用并列排名；
3、字段顺序同下表（输出结果）;
st id            name        mt score        chi score        total score
20200006    小安        83                    97                    272
20200001    小明        85                    90                    264
20200008    杰伦        88                    88                    264

4、结果按照总成绩从高到低排列:
5、一条语句(只有一个分号) 查询且没有出现基本语法错误
注:使用一条一条结果用 union 连接则为0分

解题思路：序号函数，序号函数说明

答案：

with a as (
SELECT *
from (
	SELECT 
		st_id,name,mt_score,chi_score,eng_score,
		mt_score + chi_score + eng_score as total_score,
		DENSE_RANK() over(order by mt_score desc) as mt_score_排名,
		DENSE_RANK() over(order by chi_score desc) as chi_score_排名,
		DENSE_RANK() over(order by eng_score desc) as eng_score_排名
from new_score
ORDER BY total_score desc) a
where mt_score_排名<=5
and chi_score_排名<=5
and eng_score_排名<=5
)
select 
	st_id,name,mt_score,chi_score,eng_score,total_score
from a;

效果展示：

 测试表创建：

DROP TABLE  if EXISTS `new_score`;
CREATE TABLE `new_score` (
`st_id` int ,
`name` VARCHAR(10),
`gender` char(2),
`mt_score` int,
`chi_score` int,
`eng_score` int
)
INSERT into new_score VALUES('20200001','小明','男','85','90','89');
INSERT into new_score VALUES('20200002','小宁','男','92','81','83');
INSERT into new_score VALUES('20200003','小天','男','91','90','85');
INSERT into new_score VALUES('20200004','静静','女','79','95','91');
INSERT into new_score VALUES('20200005','小玉','女','81','98','91');
INSERT into new_score VALUES('20200006','小安','女','83','97','92');
INSERT into new_score VALUES('20200007','李雷','男','77','82','99');
INSERT into new_score VALUES('20200008','杰伦','男','88','88','88');
INSERT into new_score VALUES('20200009','小娜','女','83','87','78');