SQL 查询 每个班级第n名 每组第n个 区分 分区 分组排序 DENSE_RANK() RANK() ROW_NUMBER()

背景

区分/分区/分组排序；
区分多组有序数据中的部分特典数据；

适用场景

前提：有序数据

• 查询 每组 前N、第N、后N个记录；
• 排序编号；
• 分页；

案例（一瓢饮）：每个班级第n名 ，有下表（伪数据）

s_class	s_name	score_chinese	score_maths	score_english
1	1a	90	90	90
1	1b	90	90	90
1	1c	85	85	85
1	1d	85	85	85
1	1e	80	80	80
2	2a	95	95	90
2	2b	90	88	88
2	2c	77	90	55

问题

查询每班第2名同学的总分，名称

假设：相同成绩并列排名

SQL 伪代码

SELECT
	* 
FROM
	(
	SELECT
		tmp.*,
		DENSE_RANK() over ( PARTITION BY s_class ORDER BY total_score DESC ) s_rank 
	FROM
		( SELECT ( score_english + score_chinese + score_maths ) AS total_score, s_class, s_name FROM stu_score ) tmp 
	) s 
WHERE
	s_rank = 2;

窗口函数 介绍

DENSE_RANK() over_clause

分区排序，相同则并列/并序/同序，且顺序（值）连续，一般需要配合 ORDER BY,不然 顺序权重相同

Returns the rank of the current row within its partition, without gaps. Peers are considered ties and receive the same rank. This function assigns consecutive ranks to peer groups; the result is that groups of size greater than one do not produce noncontiguous rank numbers.
返回当前行在其分区中的排名，没有间隙。同行被视为平局并获得相同的排名。此功能为对等组分配连续等级；结果是大小大于 1 的组不会产生不连续的秩数。

RANK() over_clause

分区排序，相同则同序，且顺序（值）不连续，一般需要配合 ORDER BY,不然 顺序权重相同

Returns the rank of the current row within its partition, with gaps. Peers are considered ties and receive the same rank. This function does not assign consecutive ranks to peer groups if groups of size greater than one exist; the result is noncontiguous rank numbers.
返回当前行在其分区中的排名，有间隔。同行被视为平局并获得相同的排名。如果存在大小大于 1 的组，则此函数不会为对等组分配连续的等级；结果是不连续的排名数字。

The following query shows the difference between RANK(), which produces ranks with gaps, and DENSE_RANK(), which produces ranks without gaps. The query shows rank values for each member of a set of values in the val column, which contains some duplicates. RANK() assigns peers (the duplicates) the same rank value, and the next greater value has a rank higher by the number of peers minus one. DENSE_RANK() also assigns peers the same rank value, but the next higher value has a rank one greater. For reference, the query also displays row numbers using ROW_NUMBER():
以下查询显示了 之间的区别 RANK()，它产生有间隙的等级，和DENSE_RANK()，它产生没有间隙的等级。该查询显示列中一组值的每个成员的排名值 val，其中包含一些重复项。RANK()为对等点（重复项）分配相同的排名值，并且下一个更大的值具有更高的排名，对等点的数量减去一。DENSE_RANK()还为对等点分配相同的排名值，但下一个较高值的排名大一。作为参考，查询还使用以下方式显示行号 ROW_NUMBER()：

ROW_NUMBER() over_clause

分区排序，相同不同序，且顺序（值）连续

返回其分区中当前行的编号。行数范围从 1 到分区行数。
ORDER BY 影响行编号的顺序。没有ORDER BY，行编号是不确定的。
ROW_NUMBER()为对等点分配不同的行号。要为对等点分配相同的值，请使用 RANK()或 DENSE_RANK()。例如，请参阅RANK()功能说明。

排序编号 伪案例参考

	人	分	DENSE_RANK()	RANK()	ROW_NUMBER()
甲	A	10	1	1	1
乙	B	10	1	1	2
丙	C	9	2	3	3
丁	D	8	3	4	4
戊	E	8	3	4	5
己	F	8	3	4	6
庚	G	7	4	7	7

参考

mysql 官方文档：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_dense-rank

窗口函数概念和语法：https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

常用/适用场：https://www.cnblogs.com/chengjun/p/4451421.html，
https://www.jb51.net/article/196634.htm

更新

注：不足之处，恕见谅，求指教。如有侵权，联系删除。

2022.08.15 初版
2022.10.10 修改语义