mysql分组和排序同时使用时查询数据异常

iiopsd

发布于 2022-12-23 08:46:06

2K0

发布于 2022-12-23 08:46:06

文章被收录于专栏：iiopsd技术专栏iiopsd技术专栏

问题背景：

每个地点每天新增一条数据，要根据地点分组查询出每个设备最新的数据（按创建时间倒序）。

数据库结构：

CREATE TABLE `ecord` (
  `Id` varchar(32) NOT NULL COMMENT 'Id',
  `Addr_Id` varchar(32) DEFAULT NULL COMMENT '地址Id',
  `Create_Time` datetime DEFAULT NULL COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

正常思路：

SELECT *
FROM record
GROUP BY Addr_Id
ORDER BY Create_Time DESC;

这样查询会发现查询出来的数据并不是最新的，没有得到我们需要的结果，这是因为group by 和 order by 一起使用时，会先使用group by 分组，并取出分组后的第一条数据，所以后面的order by 排序时根据取出来的第一条数据来排序的，但是第一条数据不一定是分组里面的最新的数据。

解决方案：

方案一：

使用子查询，先排序查出结果后作为临时表在分组。这里有个坑，必须要加limit，如果没有加，有些版本的数据库也无法查处正确数据。个人测试：mysql 5.6.19可以查询到正确的数据，mysql 5.7.28无法查询到正确的数据。如果数据太多加上limit可能会导致一些数据丢失，例如limit 100，却又1000个地点，就会丢失900个。

SELECT
	* 
FROM
	( 
 	SELECT * FROM Record 
 	ORDER BY Create_Time DESC 
 	LIMIT 100
  	) t 
GROUP BY
	addrId;

方案二：

可以根据时间排序，然后根据地址给数据加上排序编号。然后找出排序等于1的就可以。因为要遍历所有数据并排序，所以查询效率低。

addrId	createTime	rank
H1		2021-8-5	1
H2		2021-8-5	1
H3		2021-8-5	1
H1		2021-8-4	2
H2		2021-8-4	2
H1		2021-8-3	3

// 根据addrId 编号
SELECT @num := IF(@str = a.addr_Id , @num + 1, 1) RANK,
    @str := a.addr_Id ,
    a.*
FROM record a, (SELECT @str := '', @num := 0) t2
ORDER BY a.Create_Time DESC;

// 完整的sql：
SELECT *
FROM (
         SELECT @num := IF(@str = a.addr_Id, @num + 1, 1) RANK,
            @str := a.addr_Id ,
            a.*
         FROM record a, (SELECT @str := '', @num := 0) t
         ORDER BY a.Create_Time DESC
    ) x
WHERE RANK = 1;

方案三：

使用Max()函数，根据地址分组查出每个地址最新数据的时间，然后将查询结果关联原表查出正确的数据。当前场景个人选择这个方案解决。

SELECT
	b.*
FROM
	( 
 		SELECT Addr_Id addrId, Max(Create_Time) creatTime 
 		FROM Record 
 		GROUP BY addrId 
   ) a
LEFT JOIN record b ON a.creatTime = b.Create_Time 
AND a.addrId = b.Addr_Id

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021-08-10，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server