快捷導(dǎo)航

MySQL中通過SQL語句刪除重復(fù)記錄并且只保留一條記錄

更新時間：2023年01月04日 08:29:08 作者：?平凡?

本文主要介紹了MySQL中通過SQL語句刪除重復(fù)記錄并且只保留一條記錄，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

一、單個字段的操作

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

如何實(shí)現(xiàn) MySQL 中通過SQL語句刪除重復(fù)記錄并且只保留一條記錄_字段_02

分組介紹

如何實(shí)現(xiàn) MySQL 中通過SQL語句刪除重復(fù)記錄并且只保留一條記錄_字段_03

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

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

GROUP BY <列名序列>
HAVING <組條件表達(dá)式>

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

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

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

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

1、查詢?nèi)恐貜?fù)的數(shù)據(jù)

如何實(shí)現(xiàn) MySQL 中通過SQL語句刪除重復(fù)記錄并且只保留一條記錄_數(shù)據(jù)_04

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

2、刪除全部重復(fù)試題

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

DELETE
FROM
 dept
WHERE
 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ù)查詢出來做為一個第三方表，然后篩選更新。

如何實(shí)現(xiàn) MySQL 中通過SQL語句刪除重復(fù)記錄并且只保留一條記錄_數(shù)據(jù)_05

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

a. 第一種方法：

如何實(shí)現(xiàn) MySQL 中通過SQL語句刪除重復(fù)記錄并且只保留一條記錄_數(shù)據(jù)_06

SELECT
 *
FROM
 dept
WHERE
 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不包含剛才查出來的。這樣就查詢出了所有的重復(fù)數(shù)據(jù)（除了deptno最小的那行）。

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

c. 補(bǔ)充第三種方法：

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

4、刪除表中多余重復(fù)試題并且只留1條

a. 第一種方法：

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

b. ☆第二種方法（與上面查詢的第二種方法對應(yīng)，只是將select改為delete）：

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

c. 補(bǔ)充第三種方法（評論區(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.判斷重復(fù)的列 = tb.判斷重復(fù)的列 ) t 
 );

二、多個字段的操作

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

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

DELETE
FROM
 dept
WHERE
 (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
)

總結(jié)

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

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

到此這篇關(guān)于MySQL中通過SQL語句刪除重復(fù)記錄并且只保留一條記錄的文章就介紹到這了,更多相關(guān)MySQL SQL語句刪除重復(fù)記錄內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: