SQL经典TopN问题解法

SQL解决TopN问题：从子查询到窗口函数

最新推荐文章于 2024-03-07 09:25:02 发布

原创最新推荐文章于 2024-03-07 09:25:02 发布 · 2.7k 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#sql #mysql

本文探讨了SQL中处理TopN问题的四种方法：关联子查询用于取最大值或最小值，窗口函数如ROW_NUMBER()、RANK()和DENSE_RANK()在分组上下文中寻找最高分，设置用户变量来获取特定排名，以及利用LIMIT语句直接筛选前N条记录。通过实例解析，帮助理解不同场景下的最优解法。

SQL经典TopN问题解法

1、关联子查询
2、窗口函数
3、设置用户变量来储存第N个数值
4、limit语句

1、关联子查询

取最大值或最小值

SELECT * 
FROM score AS t1 
WHERE 成绩 = (SELECT max/min(成绩) 
			 FROM score AS t2 
			 WHERE t1.课程号 = t2.课程号);

取前两名

SELECT *
FROM score t1
WHERE (SELECT COUNT( * ) 
	   FROM score t2 
	   WHERE t1.课程号 = t2.课程号 AND t1.成绩 < t2.成绩 ) < 2
ORDER BY t1.课程号, t1.成绩 DESC; #排序便于观察

或者使用union all

# 使用union all
(SELECT * FROM score WHERE 课程号 = '0001' ORDER BY 成绩  DESC LIMIT 2)
UNION ALL
(SELECT * FROM score WHERE 课程号 = '0002' ORDER BY 成绩  DESC LIMIT 2)
UNION ALL
(SELECT * FROM score WHERE 课程号 = '0003' ORDER BY 成绩  DESC LIMIT 2);

2、窗口函数

根据需求选取ROW_NUMBER() OVER()、RANK() OVER()、DENSE_RANK() OVER()函数：

ROW_NUMBER() OVER()：依次排序且不重复，不关注值
RANK() OVER()：排序字段值相同的序号相同，且跳过相同的排名号，跳跃排序。例如[100,100,90]，则对应的排名分别为1,1,3。
DENSE_RANK() OVER()：排序字段值相同的序号相同，不跳过相同的排名号，连续排序。例如[100,100,90]，则对应的排名分别为1,1,2。

#【TOPN万能模板】
SELECT * 
FROM (SELECT *, 
			 row_number() OVER (PARTITION BY 要分组的列名 ORDER BY 要排序的列名 DESC) AS ranking 
	  FROM 表名)  AS t  
WHERE ranking <= N;

查找每个学生成绩最高的两个科目

SELECT * 
FROM (SELECT *, 
			 row_number() OVER (PARTITION BY 学号 ORDER BY 成绩 DESC) AS ranking 
	  FROM score)  AS t1  
WHERE ranking <= 2;

3、设置用户变量来储存第N个数值

查找部门工资排前2的员工

SET @second=0;
SELECT DISTINCT AVG(salary)  INTO @second
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1 OFFSET 1;

SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary)>=(SELECT @second)
ORDER BY AVG(salary) DESC

4、limit语句

查找工资排前N的员工（不分组）

# LeetCode 176-177题
Select ifnull(
          (SELECT DISTINCT Salary 
           from Employee 
           order by Salary desc 
           limit N-1,1) , null));