Mysql 如何分组查询取最新的几种方案-Toy模板网

这篇具有很好参考价值的文章主要介绍了Mysql 如何分组查询取最新的几种方案。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1.row_number函数 (mysql8以上支持)

示例：

SELECT *
FROM
( SELECT *, row_number ( ) over ( PARTITION BY 分组字段 ORDER BY 排序字段 DESC ) AS rn FROM 表 ) pca
WHERE
pca.rn = 1

解释：
1. rn字段：返回的是分组内的结果排序依次递增
2. 分组字段：进行分组的字段
3. 排序字段：需要取最新逻辑的字段

注意：

row_number函数得到的字段rn不能在表后面直接加where条件过滤，需要用select包装生成临时表pca 再进行取最新过滤
并且该函数只能在mysql8.0版本以上运行，5.7不支持该函数

2.子查询

示例：

SELECT *
FROM
表 a
WHERE
a.排序字段 = ( SELECT max( b.排序字段 ) FROM 表 b WHERE b.分组字段 = a.分组字段 )
GROUP BY
a.分组字段

解释：

分组字段：进行分组的字段
排序字段：严格意义上不叫排序字段，可以在当前sql语句中可以认为是在分组后需要找最新值字段

注意：

该子查询性能属于四种方案中最低，不适合大数据量查询使用，

最后GROUP BY分组字段是为了在多个最大值有多个相同数据情况下去重处理 按业务场景可去掉

3.临时表

示例：

创建临时表
CREATE TEMPORARY TABLE tmp_表 as select 分组字段,max(排序字段)as 排序字段 from 表 group by 排序字段;
关联临时表查询
select * from 表 p join tmp_表 tmp on tmp.分组字段= p.分组字段 and tmp.排序字段= p.排序字段

解释：

分组字段：进行分组的字段
排序字段：严格意义上不叫排序字段，可以在当前sql语句中可以认为是在分组后需要找最新值字段

注意：

先查询出最新的结果存入临时表，再进行联表查询，数据量大一点的情况下可以对创建完的临时表加索引，使整个过程的复杂度趋近f(n)。

4.新增字段标识

从业务层面优化，在表上加一个字段，isnewdata,bit型或者int型就好，每次在表中提交数据时，事务中先将要提交的数据所涉及的id，将历史数据isnewdata=1的更新为0，新提交的数据，isnewdata为1，然后增加一个索引isnewdata，或者如果需要和其他表关联的时候，增加复合索引，性能一下就上来了。

5.使用mysql用户变量提供排序序列号

示例：

SET @rank := 0;
SET @cgroup := NULL;
SELECT
a.*
IF
( @cgroup = a.分组字段, @rank := @rank + 1, @rank := 1 ) AS rank_no,
@cgroup := a.分组字段,
FROM
表 a
ORDER BY
a.分组字段,
a.排序字段 ASC

解释：

分组字段：进行分组的字段

排序字段：分组后排序的字段

@rank ：序号计数变量

@cgroup ：分组字段暂存变量，用来比较是否进入了下一组数据

运行逻辑：

该sql首先会进行排序，先分组字段，再排序字段，这点很关键，然后根据mysql的service层执行顺序对展示数据进行处理，先进入if函数判断数据是累加还是初始化@rank用户变量（注意首先第一次进来@cgroup为空），然后将分组字段的值赋予@cgroup变量。整个执行过程非常精巧有意思，得细细品味。

注意：

不支持开窗函数的mysql版本可以使用该方式，但是要ORM支持多行SQL代码块才行，还要修改数据库的SQL_MODE，mybatis和JPA都可以，Oracle不行需要配置。比如mycat这种数据库中间件