如何正確的使用一條SQL刪除重復數據

作者：數據庫干貨鋪 2023-02-26 23:31:01

數據庫中表存在重復數據，需要清理重復數據，清理后保留其中一條的情況是比較常見的需求，如何通過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

刪除SQL

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.id <>b.id

結果：

<n>查詢：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 行受到影響

刪除后數據如下：

無重復數據了。

責任編輯：華軒來源：今日頭條

SQL 數據庫

成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看