[SQL小技巧] 分组之后 组内再排序

前言

利用午休时间写写关于SQL的一些小技巧，希望正好能被需要的同学看到并使用。

有一次逛StackOverflow时看到一个妹子提出来的问题，如下图，

没人解答，看起来也不难，心想是时候轮到我装X了，答得好得到妹子的青睐后，说不定还能认识一下，以后跟人家学学英语啥的。

于是我迅速得点了一下谷歌浏览器上的翻译插件，翻译了一下问题：

大概意思就是说，她想对id相同的值分组，再在每组内额外用一列来标记组内的内容。

比如，图中两行id为10000的数据，第一行标记为10000-1，第二列标记为10000-2。

解决

SELECT
	id,
	concat(id,'_',row_number() over ( PARTITION BY id)) extra
FROM
	table
复制代码

其中的 row_number() over ( PARTITION BY id)就是本文的重点内容了，就是这么简简单单的一个小语句就能做到先对某个字段进行分组，然后在组内进行一一标记的作用。

更进一步

PARTITION BY column1,column2,……就是表示依据哪几列进行分组。与常用的group by的语法一致。

同时，它还能通过order by的语法来实现组内再排序。

如图所示，pid = 1的组内有七条数据。我想知道其中 id 为196的数据如果按照以id的大小做正序排序，在pid=1的组内它排第几怎么算呢？(很明显答案应该是排第3)。用 row_number() over PARTITION BY column_name ORDER BY column_name实现：

SELECT
	id,
	pid,
	row_number() over ( PARTITION BY pid) pid_rank,
	concat(pid,'_',row_number() over ( PARTITION BY pid)) result
FROM
	lock_test_order
复制代码

结果正确。

刚刚示例中的row_number()意思就是某个分组内的第几行的意思。此外我们还可以在 over 前更换其他函数，在分组上使用其他功能。

比如：

count(pid) over(partition by pid) total_count
复制代码

计算组内行数据总数。

说明

本教程是基于MySQL的数据库，其他数据库有的不支持该语法。

其他参考链接：www.sqlshack.com/sql-partiti…