如何正确的使用一条SQL删除重复数据

　　#头条创作挑战赛#
　　数据库中表存在重复数据，需要清理重复数据，清理后保留其中一条的情况是比较常见的需求，如何通过1条SQL准确的删除数据呢？
　　1. 创建表及测试数据
　　1.1 数据库中创建一张测试表CREATE TABLE `test` (   `id` INT  NOT NULL AUTO_INCREMENT,   `c1` VARCHAR(20) DEFAULT NULL,   `c2` VARCHAR(20) DEFAULT NULL,   `c3` INT  DEFAULT NULL,   `c4` DATETIME DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=INNODB  DEFAULT CHARSET=utf8;
　　1.2 插入测试数据INSERT INTO test(c1,c2,c3,c4) VALUES( ＂a＂,＂b＂,10, ＂2022-05-24 18:00:46＂),(＂a＂,＂c＂,20, ＂2022-05-24 18:00:46＂); INSERT INTO test(c1,c2,c3,c4) VALUES( ＂a＂,＂c＂,10, ＂2022-05-24 18:00:46＂),(＂a＂,＂b＂,20, ＂2022-05-24 18:00:46＂); INSERT INTO test(c1,c2,c3,c4) VALUES( ＂b＂,＂c＂,10, ＂2022-05-24 18:00:46＂),(＂d＂,＂b＂,20, ＂2022-05-24 18:00:46＂); INSERT INTO test(c1,c2,c3,c4) VALUES( ＂b＂,＂c＂,20, ＂2022-05-24 18:00:46＂),(＂d＂,＂b＂,30, ＂2022-05-24 18:00:46＂); INSERT INTO test(c1,c2,c3,c4) VALUES( ＂b＂,＂c＂,20, ＂2022-05-24 18:00:46＂),(＂a＂,＂b＂,40, ＂2022-05-24 18:00:46＂); INSERT INTO test(c1,c2,c3,c4) VALUES( ＂d＂,＂b＂,40, ＂2022-05-24 18:00:46＂),(＂r＂,＂f＂,40, ＂2022-05-24 18:00:46＂);
　　1.3 查看重复数据
　　例如c1,c2 这2个字段组合作为唯一条件，则查询重复数据的SQL如下SELECT   c1,   c2,   COUNT(*) FROM   test GROUP BY c1,   c2 HAVING COUNT(*) > 1;
　　可见，结果如下：
　　2. 如何删除重复数据
　　2.1 方案一
　　很多研发同学习惯的思路如下：先查出重复的记录（使用in）再查出在重复记录但id不在每组id最大值的记录直接将select 改为delete进行删除
　　查询SQL如下SELECT *    FROM  test   WHERE (c1,c2) IN ( SELECT c1,c2   FROM test  GROUP BY c1,c2  HAVING COUNT(*)>1 ) AND  id NOT  IN ( SELECT MAX(id)  FROM  test  GROUP BY c1,c2  HAVING COUNT(*)>1)  ORDER BY c1,c2 ;
　　看上去比较符合结果了，但是改为delete执行的时候结果如下：--  delete SQL DELETE FROM  test   WHERE (c1,c2) IN ( SELECT c1,c2   FROM test  GROUP BY c1,c2  HAVING COUNT(*)>1 ) AND  id NOT  IN ( SELECT MAX(id)  FROM  test  GROUP BY c1,c2  HAVING COUNT(*)>1)
　　出现报错信息：错误代码：1093 You can＂t specify target table ＂test＂ for update in FROM clause
　　也就是说MySQL里需删除的目标表在in子查询中时，不能直接执行删除操作。
　　3. 推荐写法
　　基于以上情况，使用单条SQL删除的方式如下：
　　查询SQL：SELECT  a.*   FROM  test  a , (SELECT  c1,c2,MAX(id)id FROM test  GROUP BY c1,c2 HAVING COUNT(*)>1)b WHERE    a.c1=b.c1 AND a.c2=b.c2 AND a.id <>b.id
　　删除SQLDELETE  a  FROM  test  a , (SELECT  c1,c2,MAX(id)id FROM test  GROUP BY c1,c2 HAVING COUNT(*)>1)b WHERE    a.c1=b.c1 AND a.c2=b.c2 AND a.id <>b.id
　　结果：查询：delete a FROM test a , (select c1,c2,max(id)id from test group by c1,c2 having count(*)>1)b where a.c1=b.c1 and a.c2=b.c2 and a....   共 7 行受到影响
　　删除后数据如下：
　　无重复数据了。
　　想要了解更多数据库技术文章，可关注微信公众＂数据库干货铺＂获取