分组排序取第一条数据 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}
1
2
3

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

2.1.2 取第一条

select * from t1 where rownum = 1
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 
1
2
3
4
5

2.2 变量法

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

2.2.1 数据排序

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

首先对 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
1
2
3
4
5

这条 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
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
1
2
3
4
5
6
7

3. 例子

3.1 需求

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

3.2 表结构

3.3 表数据概览

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
1
2
3
4
5
6

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; 
1
2
3
4
5
6
7
8
9
10

3.5 结果