SQL 求TOP N（多种解法）_sql topn-优快云博客

本文介绍在MySQL中查询每个科目成绩前二的同学的方法。通过四种不同的SQL实现方式：使用union all关联每科成绩前2名、利用exists和having count(*)、数字和count(*)比较以及采用分析函数row_number()/rank()/dense_rank() over()进行排名。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文以Top 2为例，使用工具：Mysql。

1. 创建原始表

create table if not exists student(
name varchar(20),
subject varchar(20),
score int(10));
insert into student values('张三','语文',76),
('张三','数学',86),
('张三','体育',88),
('李四','语文',78),
('李四','数学',80),
('李四','体育',90),
('王五','语文',60),
('王五','数学',100),
('王五','体育',71);

查看数据表：

求解：每个科目成绩前二的同学，结果如下所示：

2. 解法一

使用union all将每科成绩前2名的关联起来（不推荐）

select * from student;
(select a.* FROM student a 
where a.`subject` = '语文' ORDER BY a.score desc limit 2)
union all(
select b.* FROM student b
where b.`subject` = '数学' ORDER BY b.score desc limit 2)
union all(
select c.* FROM student c 
where c.`subject` = '体育' ORDER BY c.score desc limit 2)
ORDER BY SUBJECT desc,score desc;

结果如下：

3.解法二

使用exsits 和 having count(*) 搭配

select a.* from student a
where exists (select count(*) count from student b where a.subject = b.subject and a.score < b.score having count < 2)
order by `subject` desc,score desc;

结果如下：

4. 解法三

使用数字和count(*) 比较

select a.* from student a
where 1 > (select count(*) from student b where a.subject = b.subject and a.score < b.score)
order by subject desc,score desc;

结果如下：

5. 解法四

使用分析函数：row_number()/rank()/dense_rank() over()

根据分数排名规则选分析函数。

#row_number()
select name,subject,score from (select *,row_number() over(partition by subject order by score desc) as rk from student) t
where t.rk <= 2;
#rank()
select name,subject,score from (select *,rank() over(partition by subject order by score desc) as rk from student) t
where t.rk <= 2;
#dense_rank()
select name,subject,score from (select *,dense_rank() over(partition by subject order by score desc) as rk from student) t
where t.rk <= 2;

结果如下：