本题目使用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
解析：窗口函数的一个概念是当前行，当前行属于某个窗口，窗口由over关键字用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口：
partition by子句：按照指定字段进行分区，两个分区由边界分隔，窗口函数在不同的分区内分别执行，在跨越分区边界时重新初始化。