一条sql语句完成MySQL去重留一

前几天在做一个需求的时候，需要清理mysql中重复的记录，当时的想法是通过代码遍历写出来，然后觉得太复杂，心里想着应该可以通过一个sql语句来解决问题的。查了资料，请教了大佬之后得出了一个很便利的sql语句，这里分享下这段sql语句和思路。

需求分析

数据库中存在重复记录，删除保留其中一条（是否重复判断基准为多个字段）

解决方案

碰到这个需求的时候，心里大概是有思路的。最快想到的是可以通过一条sql语句来解决，无奈自己对于复杂sql语句的道行太浅，所以想找大佬帮忙。

找人帮忙

因为这个需求有点着急，所以最开始想到的是，可以找这方面的同行来解决，然后分享这个问题给同事，结果这货随便百度了一下，就甩给我一个从未用过的sql语句，让我自己尝试，心里万匹那啥啥啥奔腾而过...

自己百度

找到了一条sql语句：

DELETE
FROM
 vitae a
WHERE
 (a.peopleId, a.seq) IN (
  SELECT
   peopleId,
   seq
  FROM
   vitae
  GROUP BY
   peopleId,
   seq
  HAVING
   count(*) > 1
 )
AND rowid NOT IN (
 SELECT
  min(rowid)
 FROM
  vitae
 GROUP BY
  peopleId,
  seq
 HAVING
  count(*) > 1
)

这条语句是在 【MySQL中删除重复数据只保留一条】 这篇文章里找到的。这条sql思路很明显，有以下3步：

SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING count(*) > 1 查询出表中重复记录作为条件

SELECT min(rowid) FROM vitae GROUP BY peopleId, seq HAVING count(*) > 1 查询出表中重复记录中ID最小的值为第二个条件

最后根据以上两个条件，删除 除 重复记录中最小ID的其余重复记录

但是很无奈的是，运行这条语句出现了错误，大致报错意思是，不能在查询的时候同时更新这个表。

代码解决

根据上面这个sql语句想到或许可以通过代码的方式，两步来达到同样的目的：

先取出重复的数据集

根据查询到的数据集，循环删除其余的重复数据

想法是有了，写出来也很快，但是一运行吓我一跳，竟然需要 116s 左右，然后自己就想一定要找到可以使用的sql语句，贴一下代码和运行结果：

完美的【去重留一】SQL

最后在一个技术群里得到了完美的答案，看这条sql语句：

DELETE consum_record
FROM
 consum_record,
 (
  SELECT
   min(id) id,
   user_id,
   monetary,
   consume_time
  FROM
   consum_record
  GROUP BY
   user_id,
   monetary,
   consume_time
  HAVING
   count(*) > 1
 ) t2
WHERE
 consum_record.user_id = t2.user_id
 and consum_record.monetary = t2.monetary
 and consum_record.consume_time = t2.consume_time
AND consum_record.id > t2.id;

上面这条sql语句，仔细看一下，揣摩出思路也不难，大概也分为3步来理解：

(SELECT min(id) id, user_id, monetary, consume_time FROM consum_record GROUP BY user_id, monetary, consume_time HAVING count(*) > 1 ) t2 查询出重复记录形成一个集合（临时表t2），集合里是每种重复记录的最小ID

consum_record.user_id = t2.user_id and consum_record.monetary = t2.monetary and consum_record.consume_time = t2.consume_time 关联 判断重复基准的字段

根据条件，删除原表中id大于t2中id的记录

看到这个语句的时候，心里想这也太厉害了。这么一个简单的sql语句，竟然可以解决这么复杂的问题，涨姿势了~

运行起来也超级快，原先的代码循环执行，需要 116s 左右，而这里 0.3s 就可以了，厉害了~

总结

作为一个php程序猿，按理来说sql这里是不能拖后腿的，无奈实际中，需要忙碌的事情太多，现在的sql水平也只是处于在一个普通的层次中，以后找机会一定要补一下这方面的知识。

今天就分享到这里啦。

赞 (0)

相关推荐

  • mysql去重的两种方法详解及实例代码

    mysql去重 方法一:   在使用MySQL时,有时需要查询出某个字段不重复的记录,虽然mysql提供 有distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只用它来返回不重复记录的条数,而不是用它来返回不重记录的所有值.其原因是 distinct只能返回它的目标字段,而无法返回其它字段 下面先来看看例子: table id name 1 a 2 b 3 c 4 c 5 b 库结构大概这样,这只是一个简单的例子,实际情况会复杂得多. 比如我想用一条语句查询得到name不重复的所有

  • MySQL数据表合并去重的简单实现方法

    场景: 爬取的数据生成数据表,结构与另一个主表相同,需要进行合并+去重 解决:(直接举例) 首先创建两个表pep,pep2,其中pep是主表 CREATE TABLE IF NOT EXISTS `pep/pep2`( `id` INT UNSIGNED AUTO_INCREMENT, `no` VARCHAR(100) NOT NULL, PRIMARY KEY ( `id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8; 然后向pep中插入两条数据,pep2中插

  • mysql 开发技巧之JOIN 更新和数据查重/去重

    主要涉及:JOIN .JOIN 更新.GROUP BY HAVING 数据查重/去重 1 INNER JOIN.LEFT JOIN.RIGHT JOIN.FULL JOIN(MySQL 不支持).CROSS JOIN 这是在网上找到的非常好的一篇博文,图解 join 语句: CODING HORROR-A Visual Explanation of SQL Joins 下图可以很清楚的明白,join 的数据选取范围 [][1] [1]: http://7xs09x.com1.z0.glb.clo

  • Mysql删除重复的数据 Mysql数据去重复

    MySQL数据库中查询重复数据 select * from employee group by emp_name having count (*)>1; Mysql  查询可以删除的重复数据 select t1.* from employee t1 where (t1.emp_name) in (select t4.emp_name from (select t2.emp_name from employee t2 group by t2.emp_name having count(*)>1)

  • MySQL去重的方法整理

    MySQL去重的方法整理 [初级]有极少的重复行 使用distinct查出来,然后手动一行一行删除. [中级]按照单个字段的重复去重 例如:对id字段去重 使用方法:获取id的重复字段的值,利用相同id字段所在的行中,比较出数据不同的字段,删除 除了最小(或最大)的字段所在的该行之外的所有重复的行.一般使用主键来比较,因为主键的值一定是唯一值,绝对不相同. id name 1 a 1 b 2 c 2 a 3 c 结果: id name 1 a 2 a 操作: delete from a_tmp

  • mysql自联去重的一些笔记记录

    我先把功能场景简要阐述下: 数据行字段如下: name started_at type 在这张表里,name有重复值 现在需要在type确定的情况下,筛选出一个列表,满足,name不重复,找出的记录为同name下started_at为最小的记录 举个例子: 活动1 2019-06-01 type1 活动1 2019-06-02 type1 活动1 2019-06-03 type1 活动2 2019-06-03 type1 活动2 2019-06-05 type1 活动2 2019-06-07 t

  • 一条sql语句完成MySQL去重留一

    前几天在做一个需求的时候,需要清理mysql中重复的记录,当时的想法是通过代码遍历写出来,然后觉得太复杂,心里想着应该可以通过一个sql语句来解决问题的.查了资料,请教了大佬之后得出了一个很便利的sql语句,这里分享下这段sql语句和思路. 需求分析 数据库中存在重复记录,删除保留其中一条(是否重复判断基准为多个字段) 解决方案 碰到这个需求的时候,心里大概是有思路的.最快想到的是可以通过一条sql语句来解决,无奈自己对于复杂sql语句的道行太浅,所以想找大佬帮忙. 找人帮忙 因为这个需求有点着

  • 详解一条sql语句在mysql中是如何执行的

    详解一条sql语句在mysql中是如何执行的

    概览 最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在mysql中的执行流程,包括sql的查询在mysql内部会怎么流转,sql语句的更新是怎么完成的. 一.mysql架构分析 下面是mysql的一个简要架构图: mysql主要分为Server层和存储引擎层 Server层:主要包括连接器.查询缓存.分析器.优化器.执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程.触发器.视图,函数等,还有一个通用的日志模块 bing

  • 一条SQL语句在MySQL中是如何执行的

    一条SQL语句在MySQL中是如何执行的

    目录 一.mysql架构分析 1.1 连接器 1.2 查询缓存 1.3 分析器 1.4 优化器 1.5 执行器 二.语句分析 2.1 查询语句 2.2 更新语句 三.总结 一.mysql架构分析 下面是mysql的一个简要架构图: mysql主要分为Server层和存储引擎层 Server层:主要包括连接器.查询缓存.分析器.优化器.执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程.触发器.视图,函数等,还有一个通用的日志模块 binglog日志模块. 存储引擎: 主要负责数据的存储和

  • mysqli多查询特性 实现多条sql语句查询

    mysqli相对于mysql有很多优势,建议大家使用,如果没有了解,可以查看mysql的基础教程: mysqli连接数据库 和 mysqli预处理prepare使用 .不仅如此,mysqli更是支持多查询特性,看下面这段php代码: 复制代码 代码如下: <?php $mysqli = new mysqli("localhost","root","","new"); $mysqli->query("set

  • iBatis习惯用的16条SQL语句

    iBatis 简介: iBatis 是apache 的一个开源项目,一个O/R Mapping 解决方案,iBatis 最大的特点就是小巧,上手很快.如果不需要太多复杂的功能,iBatis 是能够满足你的要求又足够灵活的最简单的解决方案,现在的iBatis 已经改名为Mybatis 了. 官网为:http://www.mybatis.org/ 1.输入参数为单个值 <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLo

  • 一条SQL语句查询多个数据库

    需求:一条SQL语句从多个数据库查询出相关联的结果,输出到客户端 调查:spring框架可以配置多数据源:sql也可以将多个数据库附加到一个主数据库下 解决办法:用ATTACH DATABASE语句将多个库添加到主库下,实现功能 语法: ATTACH DATABASE 'DatabaseName' As 'Alias-Name'; 例子: ATTACH DATABASE 'C:\Users\Administrator\Desktop\sqlite_data.sq3' As 'sd'; 总结 以上

  • 腾讯面试：一条SQL语句执行得很慢的原因有哪些？---不看后悔系列(推荐)

    说实话,这个问题可以涉及到 MySQL 的很多核心知识,可以扯出一大堆,就像要考你计算机网络的知识时,问你"输入URL回车之后,究竟发生了什么"一样,看看你能说出多少了. 之前腾讯面试的实话,也问到这个问题了,不过答的很不好,之前没去想过相关原因,导致一时之间扯不出来.所以今天,我带大家来详细扯一下有哪些原因,相信你看完之后一定会有所收获,不然你打我. 开始装逼:分类讨论 一条 SQL 语句执行的很慢,那是每次执行都很慢呢?还是大多数情况下是正常的,偶尔出现很慢呢?所以我觉得,我们还得

  • python实现一次性封装多条sql语句(begin end)

    python封装利用begin end执行多条sql 因为业务需求,优化模型运行时间.考虑到sql语句每一次执行都要建立连接,查询,获取数据耗时过多.就想到将sql一起提交上去运行,能够节省很多时间.原本1.6-2.5秒耗时的sql语句经过修改后时间降到0.3-0.6秒,感觉性能提升挺好的. 当然还有一种想法,如果有python框架的orm可能会更快,相比来说耗时基本看不到了吧,这只是我的猜想,仅仅为了优化一个模型写一个框架的话 代码可能需要改的比较多,自我感觉付出和收获不一定会成正比,当然以后

  • MyBatis一次执行多条SQL语句的操作

    有个常见的场景:删除用户的时候需要先删除用户的外键关联数据,否则会触发规则报错. 解决办法不外乎有三个: 1.多条sql分批执行: 2.存储过程或函数调用: 3.sql批量执行. 今天我要说的是MyBatis中如何一次执行多条语句(使用mysql数据库). 1.修改数据库连接参数加上allowMultiQueries=true,如: hikariConfig.security.jdbcUrl=jdbc:mysql://xx.xx.xx:3306/xxxxx?characterEncoding=u

  • 52条SQL语句教你性能优化

    1, 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2,应尽量避免在 where 子句中对字段进行 null 值判断,创建表时NULL是默认值,但大多数时候应该使用NOT NULL,或者使用一个特殊的值,如0,-1作为默 认值. 3,应尽量避免在 where 子句中使用!=或<>操作符, MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE. 4,应尽量避免

随机推荐