mysql 查询每科成绩前3名

 

　　本题目使用mysql8.0新特性，窗口函数ROW_NUMBER()  OVER()解题

• 创建表结构

CREATE TABLE tb_score(
`id` bigint(20)  not null auto_increment PRIMARY KEY comment '主键' ,
stu_id int,
uname VARCHAR(20),
usubject VARCHAR(10),
score INT);

• 插入测试数据

INSERT INTO tb_score(stu_id,uname,usubject,score) VALUES

(1,'孙悟空','语文',87),

(1,'孙悟空','数学',100),

(1,'孙悟空','英语',68),

(2,'唐僧','语文',94),

(2,'唐僧','数学',56),

(2,'唐僧','英语',84),

(3,'沙僧','语文',87),

(3,'沙僧','数学',97),

(3,'沙僧','英语',84),

(4,'八戒','语文',65),

(4,'八戒','数学',85),

(4,'八戒','英语',78),

(5,'蜘蛛侠','语文',55),

(5,'蜘蛛侠','数学',97),

(5,'蜘蛛侠','英语',98),

(6,'美国队长','语文',56),

(6,'美国队长','数学',99),

(6,'美国队长','英语',87),

(7,'钢铁侠','语文',94),

(7,'钢铁侠','数学',100),

(7,'钢铁侠','英语',85);

• 查询结果

select * from tb_score

 

• 解题语句

WITH top3score AS (
SELECT  stu_id,uname,usubject,score,
ROW_NUMBER() OVER (PARTITION BY usubject ORDER BY score DESC)
AS ROW_NUM,
DENSE_RANK() OVER (PARTITION BY usubject ORDER BY score DESC) AS DENSE_RK,
RANK() OVER (PARTITION BY usubject ORDER BY score DESC) AS RK
FROM tb_score ts 
)
SELECT * FROM top3score WHERE ROW_NUM <= 3

 　   rank() ：重复不连续【跳过重复】；1224
　　dense_rank()：重复且连续【不跳过重复】；1223

　　解析：窗口函数的一个概念是当前行，当前行属于某个窗口，窗口由over关键字用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口：

partition by子句：按照指定字段进行分区，两个分区由边界分隔，窗口函数在不同的分区内分别执行，在跨越分区边界时重新初始化。

order by子句：按照指定字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。

 frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。

参考：https://baijiahao.baidu.com/s?id=1728966619393719484&wfr=spider&for=pc