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

2024-04-25 11:20

本文主要是介绍分组排序取第一条数据 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 中定义了两个变量 xrank,整个 SQL 都围绕这两个变量展开,下面分别解释一下相关的内容。

  • x 变量用于保存当前分组的字段值,比如当前分组的字段值为 1,如果后面发现分组字段值不等于 1 了则说明换到另一个分组了,需要重新计数。
  • rank 变量用于保存当前分组中上一条记录的序号,如果上一条记录的序号是 1,则当前记录序号为 2。
  • (SELECT @x:=- 1) 的作用是为 x 变量初始化一个默认值,如果不初始化默认值则 x 默认为 nullx 的默认值必须是分组字段中没有的值,不建议设置为 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 表结构

字段名字段类型说明
idBigintId
addtimeTimestamp添加时间
useridBigint用户 id
adminidBigint管理员 Id
askLongtext留言
replyLongtext回复
isreplyInt是否回复

3.3 表数据概览

IdAddtimeUseridAdminidAskReplyIsreply
12024-04-15 13:05:0011提问10
1422024-04-15 13:04:0021提问20
1432024-04-15 13:03:0031提问30
1442024-04-15 13:01:0041提问40
1822024-04-22 14:31:27171370351367011230
1832024-04-22 14:31:3317137035136701123123210
1842024-04-22 14:31:361713703513670112321330

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 rownumFROM(SELECT @x:=- 1) t0, (SELECT * FROM chat ORDER BY userid , addtime DESC) t) t2WHERE t2.rownum = 1 order by addtime desc; 

3.5 结果

AddtimeUseridAsk
2024-04-22 14:31:3617137035136701232133
2024-04-15 13:05:001提问 1
2024-04-15 13:04:002提问 2
2024-04-15 13:03:003提问 3
2024-04-15 13:01:004提问 4

这篇关于分组排序取第一条数据 SQL写法的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/934534

相关文章

SQL Server安装时候没有中文选项的解决方法

《SQLServer安装时候没有中文选项的解决方法》用户安装SQLServer时界面全英文,无中文选项,通过修改安装设置中的国家或地区为中文中国,重启安装程序后界面恢复中文,解决了问题,对SQLSe... 你是不是在安装SQL Server时候发现安装界面和别人不同,并且无论如何都没有中文选项?这个问题也

2025版mysql8.0.41 winx64 手动安装详细教程

《2025版mysql8.0.41winx64手动安装详细教程》本文指导Windows系统下MySQL安装配置,包含解压、设置环境变量、my.ini配置、初始化密码获取、服务安装与手动启动等步骤,... 目录一、下载安装包二、配置环境变量三、安装配置四、启动 mysql 服务,修改密码一、下载安装包安装地

MySQL CTE (Common Table Expressions)示例全解析

《MySQLCTE(CommonTableExpressions)示例全解析》MySQL8.0引入CTE,支持递归查询,可创建临时命名结果集,提升复杂查询的可读性与维护性,适用于层次结构数据处... 目录基本语法CTE 主要特点非递归 CTE简单 CTE 示例多 CTE 示例递归 CTE基本递归 CTE 结

MySQL多实例管理如何在一台主机上运行多个mysql

《MySQL多实例管理如何在一台主机上运行多个mysql》文章详解了在Linux主机上通过二进制方式安装MySQL多实例的步骤,涵盖端口配置、数据目录准备、初始化与启动流程,以及排错方法,适用于构建读... 目录一、什么是mysql多实例二、二进制方式安装MySQL1.获取二进制代码包2.安装基础依赖3.清

C#监听txt文档获取新数据方式

《C#监听txt文档获取新数据方式》文章介绍通过监听txt文件获取最新数据,并实现开机自启动、禁用窗口关闭按钮、阻止Ctrl+C中断及防止程序退出等功能,代码整合于主函数中,供参考学习... 目录前言一、监听txt文档增加数据二、其他功能1. 设置开机自启动2. 禁止控制台窗口关闭按钮3. 阻止Ctrl +

java如何实现高并发场景下三级缓存的数据一致性

《java如何实现高并发场景下三级缓存的数据一致性》这篇文章主要为大家详细介绍了java如何实现高并发场景下三级缓存的数据一致性,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 下面代码是一个使用Java和Redisson实现的三级缓存服务,主要功能包括:1.缓存结构:本地缓存:使

详解MySQL中JSON数据类型用法及与传统JSON字符串对比

《详解MySQL中JSON数据类型用法及与传统JSON字符串对比》MySQL从5.7版本开始引入了JSON数据类型,专门用于存储JSON格式的数据,本文将为大家简单介绍一下MySQL中JSON数据类型... 目录前言基本用法jsON数据类型 vs 传统JSON字符串1. 存储方式2. 查询方式对比3. 索引

在MySQL中实现冷热数据分离的方法及使用场景底层原理解析

《在MySQL中实现冷热数据分离的方法及使用场景底层原理解析》MySQL冷热数据分离通过分表/分区策略、数据归档和索引优化,将频繁访问的热数据与冷数据分开存储,提升查询效率并降低存储成本,适用于高并发... 目录实现冷热数据分离1. 分表策略2. 使用分区表3. 数据归档与迁移在mysql中实现冷热数据分

C#解析JSON数据全攻略指南

《C#解析JSON数据全攻略指南》这篇文章主要为大家详细介绍了使用C#解析JSON数据全攻略指南,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录一、为什么jsON是C#开发必修课?二、四步搞定网络JSON数据1. 获取数据 - HttpClient最佳实践2. 动态解析 - 快速

SpringBoot集成MyBatis实现SQL拦截器的实战指南

《SpringBoot集成MyBatis实现SQL拦截器的实战指南》这篇文章主要为大家详细介绍了SpringBoot集成MyBatis实现SQL拦截器的相关知识,文中的示例代码讲解详细,有需要的小伙伴... 目录一、为什么需要SQL拦截器?二、MyBATis拦截器基础2.1 核心接口:Interceptor