作者：千g
來源：blog.csdn.net/n950814abc/article/details/82284838

最近，在做題庫系統(tǒng)，由于在題庫中添加了重復的試題，所以需要查詢出重復的試題，并且刪除掉重復的試題只保留其中1條，以保證考試的時候抽不到重復的題。

首先寫了一個小的例子：

一、單個字段的操作

這是數(shù)據(jù)庫中的表：

分組介紹

Select 重復字段 From 表 Group By 重復字段 Having Count(*)>1

查看是否有重復的數(shù)據(jù)：

GROUP BY <列名序列>
HAVING <組條件表達式>

查詢出：根據(jù)dname分組，同時滿足having字句中組條件表達式（重復次數(shù)大于1）的那些組

count(*)與count(1) 其實沒有什么差別，用哪個都可以

count(*)與count（列名）的區(qū)別：

count(*)將返回表格中所有存在的行的總數(shù)包括值為null的行，然而count(列名)將返回表格中除去null以外的所有行的總數(shù)(有默認值的列也會被計入）。

1. 查詢?nèi)恐貜偷臄?shù)據(jù)

Select * From 表 Where 重復字段 In (Select 重復字段 From 表 Group By 重復字段 Having Count(*)>1)

2. 刪除全部重復試題

將上面的查詢select改為delete（這樣會出錯的）

DELETEFROM deptWHERE dname IN (  SELECT   dname  FROM   dept  GROUP BY   dname  HAVING   count(1) > 1 )

會出現(xiàn)如下錯誤：[Err] 1093 - You can't specify target table 'dept' for update in FROM clause

原因是：更新這個表的同時又查詢了這個表，查詢這個表的同時又去更新了這個表，可以理解為死鎖。mysql不支持這種更新查詢同一張表的操作

解決辦法：把要更新的幾列數(shù)據(jù)查詢出來做為一個第三方表，然后篩選更新。

3. 查詢表中多余重復試題（根據(jù)depno來判斷，除了rowid最小的一個）

a. 第一種方法

SELECT *FROM deptWHERE dname IN (  SELECT   dname  FROM   dept  GROUP BY   dname  HAVING   COUNT(1) > 1 )AND deptno NOT IN ( SELECT  MIN(deptno) FROM  dept GROUP BY  dname HAVING  COUNT(1) > 1)

上面這種寫法正確，但是查詢的速度太慢，可以試一下下面這種方法：

b. 第二種方法

根據(jù)dname分組，查找出deptno最小的。然后再查找deptno不包含剛才查出來的。這樣就查詢出了所有的重復數(shù)據(jù)（除了deptno最小的那行）。

SELECT *FROM deptWHERE deptno NOT IN (  SELECT   dt.minno  FROM   (    SELECT     MIN(deptno) AS minno    FROM     dept    GROUP BY     dname   ) dt )

c. 補充第三種方法

SELECT * FROM table_name AS ta WHERE ta.唯一鍵 <> ( SELECT max( tb.唯一鍵 ) FROM table_name AS tb WHERE ta.判斷重復的列 = tb.判斷重復的列 );

4. 刪除表中多余重復試題并且只留1條

a. 第一種方法：

DELETEFROM deptWHERE dname IN (  SELECT   t.dname  FROM   (    SELECT     dname    FROM     dept    GROUP BY     dname    HAVING     count(1) > 1   ) t )AND deptno NOT IN (SELECT dt.mindeptnoFROM (  SELECT   min(deptno) AS mindeptno  FROM   dept  GROUP BY   dname  HAVING   count(1) > 1 ) dt)

b. 第二種方法（與上面查詢的第二種方法對應，只是將select改為delete）

DELETEFROM deptWHERE deptno NOT IN (  SELECT   dt.minno  FROM   (    SELECT     MIN(deptno) AS minno    FROM     dept    GROUP BY     dname   ) dt )

c. 補充第三種方法（評論區(qū)推薦的一種方法）

DELETE FROM table_name AS ta WHERE ta.唯一鍵 <> (SELECT t.maxid FROM ( SELECT max( tb.唯一鍵 ) AS maxid FROM table_name AS tb WHERE ta.判斷重復的列 = tb.判斷重復的列 ) t  );

二、多個字段的操作

單個字段的如果會了，多個字段也非常簡單。就是將group by 的字段增加為你想要的即可。

此處只寫一個，其他方法請仿照一個字段的寫即可。

DELETEFROM deptWHERE (dname, db_source) IN (  SELECT   t.dname,   t.db_source  FROM   (    SELECT     dname,     db_source    FROM     dept    GROUP BY     dname,     db_source    HAVING     count(1) > 1   ) t )AND deptno NOT IN ( SELECT  dt.mindeptno FROM  (   SELECT    min(deptno) AS mindeptno   FROM    dept   GROUP BY    dname,    db_source   HAVING    count(1) > 1  ) dt)

# 總結

其實上面的方法還有很多需要優(yōu)化的地方，如果數(shù)據(jù)量太大的話，執(zhí)行起來很慢，可以考慮加優(yōu)化一下：

在經(jīng)常查詢的字段上加上索引
將*改為你需要查詢出來的字段，不要全部查詢出來
小表驅(qū)動大表用IN，大表驅(qū)動小表用EXISTS。IN適合的情況是外表數(shù)據(jù)量小的情況，而不是外表數(shù)據(jù)大的情況，因為IN會遍歷外表的全部數(shù)據(jù)，假設a表100條，b表10000條那么遍歷次數(shù)就是100*10000次，而exists則是執(zhí)行100次去判斷a表中的數(shù)據(jù)是否在b表中存在，它只執(zhí)行了a.length次數(shù)。至于哪一個效率高是要看情況的，因為in是在內(nèi)存中比較的，而exists則是進行數(shù)據(jù)庫查詢操作的。

感謝您的閱讀，也歡迎您發(fā)表關于這篇文章的任何建議，關注我，技術不迷茫！小編到你上高速。

· END ·

最后，關注公眾號互聯(lián)網(wǎng)架構師，在后臺回復：2T，可以獲取我整理的 Java 系列面試題和答案，非常齊全。

正文結束

京東筆試題：如何實現(xiàn) MySQL 刪除重復記錄并且只保留一條？

一、單個字段的操作