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

1. 背景

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

2. 解决方案

目前我接触到的解决方案有两种，分别是开窗函数

row_number

和变量法。

2.1 开窗函数法

比较常用的解决方案是使用开窗函数

row_number() over(partition by xxx order by xxx）

。使用开窗函数比较简便，只需要两个步骤

2.1.1 分组+排序+标注序号

select*, 
    row_number()over(partitionby ${group_col} orderby ${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(partitionby ${group_col} orderby ${order_col} )  rownum 
    from ${table}) t1 
where rownum =1

2.2 变量法

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

2.2.1 数据排序

select*from ${table} orderby ${group_col}, ${order_col}

首先对

table

表中的数据进行排序，排序的字段中第一个必须是想要用于分组的字段

group_col

，后面才是需要排序的字段

order_col

。

2.2.2 添加序号

SELECT t.*,IF(@x= ${group_col} OR(@xISNULLAND ${group_col} ISNULL),@rank:=@rank+1,@rank:=1AND@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(@xISNULLAND ${group_col} ISNULL),@rank:=@rank+1,@rank:=1AND@x:= ${group_col})as rownum 
    from(SELECT@x:=-1)as t0,(select*from ${table} orderby ${group_col}, ${order_col})as t)as t2 where t2.rownum =1

3. 例子

3.1 需求

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

3.2 表结构

字段名字段类型说明idBigintIdaddtimeTimestamp添加时间useridBigint用户 idadminidBigint管理员 IdaskLongtext留言replyLongtext回复isreplyInt是否回复

3.3 表数据概览

IdAddtimeUseridAdminidAskReplyIsreply12024-04-15 13:05:0011提问101422024-04-15 13:04:0021提问201432024-04-15 13:03:0031提问301442024-04-15 13:01:0041提问401822024-04-22 14:31:271713703513670112301832024-04-22 14:31:33171370351367011231232101842024-04-22 14:31:361713703513670112321330

3.4 查询语句

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

3.4.1 开窗函数法

select*from(select*, 
        row_number()over(partitionby userid orderby addtime desc)  rownum 
        from chat 
        where ask isnotnull) t1 
    where rownum =1orderby addtime desc

3.4.2 变量法

select addtime, userid, ask from((select*,IF(@x= userid OR(@xISNULLAND userid ISNULL),@rank:=@rank+1,@rank:=1AND@x:=userid)AS rownum
    FROM(SELECT@x:=-1) t0,(SELECT*FROM chat 
                ORDERBY userid , addtime DESC) t) t2
    WHERE t2.rownum =1orderby addtime desc;

3.5 结果

AddtimeUseridAsk2024-04-22 14:31:36171370351367012321332024-04-15 13:05:001提问 12024-04-15 13:04:002提问 22024-04-15 13:03:003提问 32024-04-15 13:01:004提问 4