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

引自: https://blog.csdn.net/iiopsd/article/details/119572410

 

问题背景：

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

数据库结构：

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