分组排序取第一条数据 SQL写法

1. 背景

在数据库查询过程中经常遇到需要分组排序查询第一条数据的情况。例如，在消息列表中需要展示每个联系人最近的一条信息。

2. 解决方案

目前我接触到的解决方案有两种，分别是开窗函数 row_number 和变量法。

2.1 开窗函数法

比较常用的解决方案是使用开窗函数 row_number() over(partition by xxx order by xxx） 。使用开窗函数比较简便，只需要两个步骤

2.1.1 分组+排序+标注序号

select *, 
	row_number()  over (partition by ${group_col} order by ${order_col} ) as rownum 
from ${table}

以上 SQL 中 ${group_col} 表示要分组的字段；${order_col} 表示排序字段；rownum 是为序号字段取的别名。这条 SQL 的含义是将数据表 table 中的数据根据 group_col 分组后根据 order_col 进行排序，并为每条数据标注出在当前分组中的序号。

2.1.2 取第一条

select * from t1 where rownum = 1

• t1 是 分组+排序+标注序号 后的表，这里为了简洁就直接用 t1 代表子查询了。
• rownum 是 分组+排序+标注序号 中序号的别名。where rownum = 1 表示取 分组+排序+标注序号 后的第一条数据。

SQL 的含义是从 分组+排序+标注序号 后的表中查询序号为 1 的数据，这里的序号是各分组中排序后的序号。

3.1.3 Demo

将上述两个步骤连贯起来的 Demo 如下。

select * from (
	select *, 
	row_number() over (partition by ${group_col} order by ${order_col} )  rownum 
	from ${table}) t1 
where rownum = 1 

2.2 变量法

开窗函数的方法在 Hive 和高版本的 MySQL 里都是比较简单的查询方法，但是在低版本的 MySQL （比如 MySQL 5.7）中不支持开窗函数。所以只能换另一种方法，就是变量法。变量法需要通过变量来达成开窗函数的效果所以比较复杂，主要分为三部：数据排序、添加序号、取第一条。

2.2.1 数据排序

select * from ${table} order by ${group_col}, ${order_col}

首先对 table 表中的数据进行排序，排序的字段中第一个必须是想要用于分组的字段 group_col，后面才是需要排序的字段 order_col。

2.2.2 添加序号

SELECT t.*, 
	IF(@x = ${group_col} OR (@x IS NULL AND ${group_col} IS NULL), 
		@rank:=@rank + 1, 
		@rank:=1 AND @x:= ${group_col}) as rownum 
	from (SELECT @x:=- 1) t0, t

这条 SQL 中定义了两个变量 x 和 rank，整个 SQL 都围绕这两个变量展开，下面分别解释一下相关的内容。

• x 变量用于保存当前分组的字段值，比如当前分组的字段值为 1，如果后面发现分组字段值不等于 1 了则说明换到另一个分组了，需要重新计数。
• rank 变量用于保存当前分组中上一条记录的序号，如果上一条记录的序号是 1，则当前记录序号为 2。
• (SELECT @x:=- 1) 的作用是为 x 变量初始化一个默认值，如果不初始化默认值则 x 默认为 null。x 的默认值必须是分组字段中没有的值，不建议设置为 null。
• IF(@x = ${group_col} OR (@x IS NULL AND ${group_col} IS NULL), @rank:=@rank + 1, @rank:=1 AND @x:=${group_col}) as rownum 这条语句就是序号赋值的关键所在。含义为如果 x 与当前记录的分组字段值相同则 rank = rank + 1（同分组内序号递增），否则 rank=1 （不同分组重新开始计数）并且 @x:=${group_col}（将 x 赋值为当前记录的分组字段值）。
• t 为 数据排序 后的表，这里为了简洁用 t 来代替子查询。

2.2.3 取第一条

select * from t2 where t2.rownum = 1

t2 是 添加序号 后的表，这里为了简洁也是用 t2 代替子查询。

2.2.4 Demo

将上述三个步骤融合在一起如下。

select * from (
	SELECT t.*, 
		IF(@x = ${group_col} OR (@x IS NULL AND ${group_col} IS NULL), 		
		@rank:=@rank + 1, 
		@rank:=1 AND @x:= ${group_col}) as rownum 
	from (SELECT @x:=- 1) as t0, 
	(select * from ${table} order by ${group_col}, ${order_col}) as t) as t2 where t2.rownum = 1

3. 例子

3.1 需求

有一张留言记录表，表中记录的是每个用户的留言和管理员的回复。现需要取每个用户最新的一条留言记录。

3.2 表结构

字段名	字段类型	说明
id	Bigint	Id
addtime	Timestamp	添加时间
userid	Bigint	用户 id
adminid	Bigint	管理员 Id
ask	Longtext	留言
reply	Longtext	回复
isreply	Int	是否回复

3.3 表数据概览

Id	Addtime	Userid	Adminid	Ask	Reply	Isreply
1	2024-04-15 13:05:00	1	1	提问1		0
142	2024-04-15 13:04:00	2	1	提问2		0
143	2024-04-15 13:03:00	3	1	提问3		0
144	2024-04-15 13:01:00	4	1	提问4		0
182	2024-04-22 14:31:27	1713703513670	1	123		0
183	2024-04-22 14:31:33	1713703513670	1	12312321		0
184	2024-04-22 14:31:36	1713703513670	1	1232133		0

3.4 查询语句

查询的思路就是根据用户 Id 分组，按添加时间倒序（从大到小）排，取每个用户 Id 分组中的第一条数据。为了方便查看，在查询最后再将记录根据添加时间倒序排列，将留言时间最近的用户放在前面。

3.4.1 开窗函数法

select * from (
	select *, 
		row_number()  over (partition by userid order by addtime desc)  rownum 
		from chat 
		where ask is not null ) t1 
	where rownum = 1 order by addtime desc

3.4.2 变量法

select addtime, userid, ask from (
    (select *, 
		IF(@x = userid OR (@x IS NULL AND userid IS NULL), 
			@rank:=@rank + 1, 
			@rank:=1 AND @x:=userid) AS rownum
    FROM
		(SELECT @x:=- 1) t0, 
			(SELECT * FROM chat 
				ORDER BY userid , addtime DESC) t) t2
	WHERE t2.rownum = 1 order by addtime desc; 

3.5 结果

Addtime	Userid	Ask
2024-04-22 14:31:36	1713703513670	1232133
2024-04-15 13:05:00	1	提问 1
2024-04-15 13:04:00	2	提问 2
2024-04-15 13:03:00	3	提问 3
2024-04-15 13:01:00	4	提问 4