记录一次mysql处理重复数据

最新推荐文章于 2024-08-29 17:00:41 发布

if时光重来

最新推荐文章于 2024-08-29 17:00:41 发布

阅读量69

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/admin_web/article/details/130226550

版权

mysql 专栏收录该内容

29 篇文章 0 订阅

订阅专栏

该文描述了如何使用SQL查询来找出并处理数据库中reportid重复的数据。首先，它查询出有重复reportid的记录，然后找出这些重复组内odate最大或id最大的记录，最后执行删除操作，保留每个reportid组内的最大odate或最大id记录。

摘要由CSDN通过智能技术生成

// 查询reportid重复的数据
select reportid,count(*) from tables GROUP BY reportid HAVING count(*)>1
// 查询reportid重复的数据中最大操作时间的id数据
select t1.id,t1.reportid,t1.odate from tables t1 INNER JOIN (select reportid,max(odate) max_odate from tables GROUP BY reportid HAVING count(*)>1) t2 on t1.reportid=t2.reportid and t1.odate=t2.max_odate
// 删除重复数据，保留分组时odate最大的一条数据
delete from tables where reportid in ( select t.reportid from (select reportid,count(*) from tables GROUP BY reportid HAVING count(*)>1) t) and id not in (select t2.id from (select t1.id,t1.reportid,t1.odate from tables t1 INNER JOIN (select reportid,max(odate) max_odate from tables GROUP BY reportid HAVING count(*)>1) t2 on t1.reportid=t2.reportid and t1.odate=t2.max_odate) t2)

// 删除重复数据保留id最大的一条
delete from tables where reportid in ( select t.reportid from (select reportid,count(*) from tables GROUP BY reportid HAVING count(*)>1) t) and id not in (select t2.id from (select max(id) id,reportid,count(*) from tables GROUP BY reportid HAVING count(*)>1) t2)