mysql 分组后取距离当前时间最近的一条

一、业务场景

记录不同类型数据的历史数据，希望读取到个类型最新的一条数据（离当前时间最近的一条），其中monitoring_type是用了分组的类型，monitoring_data是数据，monitoring_time是用来排序的时间

原数据：

希望达到的效果

二、具体实现

1、第一中写法：先对源数据排序然后分组

SELECT a.*  FROM
	( SELECT DISTINCT id, monitoring_type, monitoring_data, 
      monitoring_unit FROM dv_monitoring ORDER BY monitoring_time DESC ) a 
GROUP BY  monitoring_type
---使用下面这种写法-------------------------------------------------------------
SELECT a.*  FROM
	( SELECT DISTINCT id, monitoring_type, monitoring_data, monitoring_unit,monitoring_time  
FROM dv_monitoring ORDER BY monitoring_time DESC ) a 
GROUP BY monitoring_type
------稳得一批的写法--------------------------------------------------------
SELECT a.monitoring_type, any_value(a.monitoring_data) monitoring_data, any_value(a.monitoring_unit) monitoring_unit 
FROM
(SELECT DISTINCT id,monitoring_type, monitoring_data，monitoring_unit,monitoring_time 
FROM dv_monitoring   ORDER BY monitoring_time desc
) a GROUP BY a.monitoring_type

解释：

1、先看括号里面的sql，将源数据通过 monitoring_time 字段进行降序处理，这里使用DISTINCT 关键字将这括号里面的sql产生的临时表固定下来，但是这里会有个问题在mysql 8.0.30版本且没有只忽略大小写的情况下会报错，原因是distinct执行时会对查询的记录进行去重，产生一张虚拟的临时表，order by的排序对象是临时表，但是里面没有monitoring_time 字段，（详情参考【distinct】与【order by】结合使用时注意点）所以需要把排序的字段给加上。

同时从mysql5.7开始，子查询的排序已经变为无效了，可以使用limit 10000，这样子查询就不光是查询同时排序也会生效，能达到与DISTINCT 同样的效果，但有条数限制10000

2、临时表有个后我们就得到了一个时间是降序的数据表，在对这个数据表进行分组，注意：这里可能会报一个sql_mode=only_full_group_by的错误，这是因为MySQL5.7后默认开启了SQL_MODE严格模式，如果没有关闭的话就需要使用any_value() 函数将需要展示的非group_by的字段给包裹起来，（可以参考sql_mode=only_full_group_by 4种解决方法含举例）

分组后默认展示的是第一数据，这样就可以得到想要的数据了

2、第二种写法：先分组再排序后取第一条

select
  *
from
 (
     select
     SUBSTRING_INDEX(GROUP_CONCAT( distinct id order by monitoring_time desc), ',', 1) idMax
     from
     dv_monitoring
     group by monitoring_type
 ) a
 left join dv_monitoring ld on a.idMax = ld.id
 order by monitoring_time desc

解释：

（1）GROUP_CONCAT( distinct id order by monitoring_time desc) GROUP_CONCAT函数是将多个字符串连接成单个字符串，这串代码的意思是对monitoring_time 字段进行降序排序后，取对应的id通过DISTINCT 去重后，按照排序结果的顺序用‘,’拼接起来

（2）SUBSTRING_INDEX(GROUP_CONCAT( distinct id order by monitoring_time desc), ',', 1) 在将id拼接起来的结果上使用SUBSTRING_INDEX函数取出排在第一个的id，最后连接原表查询取出的id对应的数据。

参考：mysql 分组排序后取第一条数据

其他参考资料：Mysql分组排序取每组第一条（二种实现方式）