mysql分组和排序同时使用时查询数据异常
2023-09-27 14:21:27 时间
引自: 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
相关文章
- mysql-异常Incorrect string value: 'xF0x9F...' for column 'XXX' at row 1
- mysql-异常
- 部署tidb同步到mysql(drainer)
- mysql异常处理
- mysql中子查询更新,得用别名表
- Java 连接mysql的jdbcutil代码
- mysql 去除重复记录数据
- 记一次 MySQL 主从同步异常的排查记录,百转千回
- mysql命令行工具
- mysql 命令
- MySQL 高可用架构 - MHA环境部署记录
- 查询mysql数据库启动时间抛异常
- mysql int(1) 与 tinyint(1) 有什么区别?
- [数据库/MySQL]解决异常:Data truncation: Truncated incorrect DOUBLE value: 'dc5'
- mysql语句分析
- mybatis 异常 too many connections 解决方案 mysql
- 玩转Mysql系列 - 第20篇:异常捕获及处理详解
- 已解决java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver异常的正确解决方法,亲测有效!!!
- 已解决java.lang.ClassNotFoundException: com.mysql.jdbc.Driver异常的正确解决方法,亲测有效!!!
- mysql 添加 删除字段
- lightdb/postgresql/oracle/mysql/sql server中各自绑定变量的写法
- mycat下mysql jdbc connector使用高版本报PacketTooBigException异常
- MYSQL错误解决:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
- com.mysql.jdbc.exceptions.jdbc4.CommunicationsException/com.atomikos.datasource.ResourceException异常解决
- Linux(CentOS)中常用软件安装,使用及异常——MySQL, VmTools
- 【mysql我能讲两小时034】purge操作有什么作用?