SQL查找刪除重複行

2021-02-19 Java知音

點擊上方「Java知音」，選擇「置頂公眾號」

技術文章第一時間送達！


本文講述如何查找資料庫裡重複的行。這是初學者十分普遍遇到的問題。方法也很簡單。這個問題還可以有其他演變，例如，如何查找「兩欄位重複的行」（#mysql IRC 頻道問到的問題）


如何查找重複行

        第一步是定義什麼樣的行才是重複行。多數情況下很簡單：它們某一列具有相同的值。本文採用這一定義，或許你對「重複」的定義比這複雜，你需要對sql做些修改。


本文要用到的數據樣本

create table test(id int not null primary key, day date not null);  
 
insert into test(id, day) values(1, '2006-10-08');  
insert into test(id, day) values(2, '2006-10-08');  
insert into test(id, day) values(3, '2006-10-09');  
 
select * from test;  
+
| id | day        |  
+
|  1 | 2006-10-08 |  
|  2 | 2006-10-08 |  
|  3 | 2006-10-09 |  
+

前面兩行在day欄位具有相同的值，因此如何我將他們當做重複行，這裡有一查詢語句可以查找。查詢語句使用GROUP BY子句把具有相同欄位值的行歸為一組，然後計算組的大小。

select day, count(*) from test GROUP BY day;  
+--++  
| day        | count(*) |  
+--++  
| 2006-10-08 |        2 |  
| 2006-10-09 |        1 |  
+--++

重複行的組大小大於1。如何希望只顯示重複行，必須使用HAVING子句，比如

select day, count(*) from test group by day HAVING count(*) > 1;  
+--++  
| day        | count(*) |  
+--++  
| 2006-10-08 |        2 |  
+--++

這是基本的技巧：根據具有相同值的欄位分組，然後知顯示大小大於1的組。


為什麼不能使用WHERE子句？

        因為WHERE子句過濾的是分組之前的行，HAVING子句過濾的是分組之後的行。


如何刪除重複行

        一個相關的問題是如何刪除重複行。一個常見的任務是，重複行只保留一行，其他刪除，然後你可以創建適當的索引，防止以後再有重複的行寫入資料庫。
同樣，首先是弄清楚重複行的定義。你要保留的是哪一行呢？第一行，或者某個欄位具有最大值的行？本文中，假設要保留的是第一行——id欄位具有最小值的行，意味著你要刪除其他的行。


        也許最簡單的方法是通過臨時表。尤其對於MYSQL，有些限制是不能在一個查詢語句中select的同時update一個表。簡單起見，這裡只用到了臨時表的方法。
我們的任務是：刪除所有重複行，除了分組中id欄位具有最小值的行。因此，需要找出大小大於1的分組，以及希望保留的行。你可以使用MIN()函數。這裡的語句是創建臨時表，以及查找需要用DELETE刪除的行。

create temporary table to_delete (day date not null, min_id int not null);  
     
   insert into to_delete(day, min_id)  
      select day, MIN(id) from test group by day having count(*) > 1;  
     
   select * from to_delete;  
   +
   | day        | min_id |  
   +
   | 2006-10-08 |      1 |  
   +

有了這些數據，你可以開始刪除「髒數據」行了。可以有幾種方法，各有優劣（詳見我的文章many-to-one problems in SQL），但這裡不做詳細比較，只是說明在支持查詢子句的關係資料庫中，使用的標準方法。

delete from test  
  where exists(  
     select * from to_delete  
     where to_delete.day = test.day and to_delete.min_id <> test.id  
  )

如何查找多列上的重複行

有人最近問到這樣的問題：
我的一個表上有兩個欄位b和c，分別關聯到其他兩個表的b和c欄位。我想要找出在b欄位或者c欄位上具有重複值的行。


        咋看很難明白，通過對話後我理解了：他想要對b和c分別創建unique索引。如上所述，查找在某一欄位上具有重複值的行很簡單，只要用group分組，然後計算組的大小。並且查找全部欄位重複的行也很簡單，只要把所有欄位放到group子句。但如果是判斷b欄位重複或者c欄位重複，問題困難得多。這裡提問者用到的樣本數據

create table a_b_c(  
  a int not null primary key auto_increment,  
  b int,  
  c int  
);  
 
insert into a_b_c(b,c) values (1, 1);  
insert into a_b_c(b,c) values (1, 2);  
insert into a_b_c(b,c) values (1, 3);  
insert into a_b_c(b,c) values (2, 1);  
insert into a_b_c(b,c) values (2, 2);  
insert into a_b_c(b,c) values (2, 3);  
insert into a_b_c(b,c) values (3, 1);  
insert into a_b_c(b,c) values (3, 2);  
insert into a_b_c(b,c) values (3, 3);

現在，你可以輕易看到表裡面有一些重複的行，但找不到兩行具有相同的二元組{b, c}。這就是為什麼問題會變得困難了。


錯誤的查詢語句

        如果把兩列放在一起分組，你會得到不同的結果，具體看如何分組和計算大小。提問者恰恰是困在了這裡。有時候查詢語句找到一些重複行卻漏了其他的。這是他用到了查詢  

select b, c, count(*) from a_b_c  
group by b, c  
having count(distinct b > 1)  
  or count(distinct c > 1);

結果返回所有的行，因為CONT(*)總是1.為什麼？因為 >1 寫在COUNT()裡面。這個錯誤很容易被忽略，事實上等效於

select b, c, count(*) from a_b_c  
group by b, c  
having count(1)  
  or count(1);

為什麼？因為(b > 1)是一個布爾值，根本不是你想要的結果。你要的是

select b, c, count(*) from a_b_c  
group by b, c  
having count(distinct b) > 1  
  or count(distinct c) > 1;

返回空結果。很顯然，因為沒有重複的{b,c}。這人試了很多其他的OR和AND的組合，用來分組的是一個欄位，計算大小的是另一個欄位，像這樣

select b, count(*) from a_b_c group by b having count(distinct c) > 1;  
   +-++  
   | b    | count(*) |  
   +-++  
   |    1 |        3 |  
   |    2 |        3 |  
   |    3 |        3 |  
   +-++

沒有一個能夠找出全部的重複行。而且最令人沮喪的是，對於某些情況，這種語句是有效的，如果錯誤地以為就是這麼寫法，然而對於另外的情況，很可能得到錯誤結果。

        事實上，單純用GROUP BY 是不可行的。為什麼？因為當你對某一欄位使用group by時，就會把另一欄位的值分散到不同的分組裡。對這些欄位排序可以看到這些效果，正如分組做的那樣。首先，對b欄位排序，看看它是如何分組的

a   b   c
7   1   1
8   1   2
9   1   3
10   2   1
11   2   2
12   2   3
13   3   1
14   3   2
15   3   3

 當你對b欄位排序（分組），相同值的c被分到不同的組，因此不能用COUNT(DISTINCT c)來計算大小。COUNT()之類的內部函數隻作用於同一個分組，對於不同分組的行就無能為力了。類似，如果排序的是c欄位，相同值的b也會分到不同的組，無論如何是不能達到我們的目的的。


幾種正確的方法

        也許最簡單的方法是分別對某個欄位查找重複行，然後用UNION拼在一起，像這樣：

select b as value, count(*) as cnt, 'b' as what_col  
    from a_b_c group by b having count(*) > 1  
    union  
    select c as value, count(*) as cnt, 'c' as what_col  
    from a_b_c group by c having count(*) > 1;  
   +
   | value | cnt | what_col |  
   +
   |     1 |   3 | b        |  
   |     2 |   3 | b        |  
   |     3 |   3 | b        |  
   |     1 |   3 | c        |  
   |     2 |   3 | c        |  
   |     3 |   3 | c        |  
   +

輸出what_col欄位為了提示重複的是哪個欄位。另一個辦法是使用嵌套查詢：

select a, b, c from a_b_c  
where b in (select b from a_b_c group by b having count(*) > 1)  
   or c in (select c from a_b_c group by c having count(*) > 1);  
+----+-+-+  
| a  | b    | c    |  
+----+-+-+  
|  7 |    1 |    1 |  
|  8 |    1 |    2 |  
|  9 |    1 |    3 |  
| 10 |    2 |    1 |  
| 11 |    2 |    2 |  
| 12 |    2 |    3 |  
| 13 |    3 |    1 |  
| 14 |    3 |    2 |  
| 15 |    3 |    3 |  
+----+-+-+

這種方法的效率要比使用UNION低許多，並且顯示每一重複的行，而不是重複的欄位值。還有一種方法，將自己跟group的嵌套查詢結果聯表查詢。寫法比較複雜，但對於複雜的數據或者對效率有較高要求的情況，是很有必要的。


select a, a_b_c.b, a_b_c.c  
   from a_b_c  
      left outer join (  
         select b from a_b_c group by b having count(*) > 1  
      ) as b on a_b_c.b = b.b  
      left outer join (  
         select c from a_b_c group by c having count(*) > 1  
      ) as c on a_b_c.c = c.c  
   where b.b is not null or c.c is not null

以上方法可行，我敢肯定還有其他的方法。如果UNION能用，我想會是最簡單不過的了。


推薦

技術文章：Java中的設計模式

源碼分享：Javaweb練手項目下載



相關焦點

  • sql刪除重複語句
    2、刪除重複的列中ID較小的那一個，保留較大的iddelete t1 from test t1inner join test t2wheret1.id < t2.id ANDt1ALTER table `表名` add PRIMARY KEY (user_role_id) 二、MSSQL--查找
  • 如何在Linux中查找和刪除重複文件
    在Linux系作業系統中查找並刪除重複文件的方法的確有很多，不過這裡介紹的是一款非常簡單實用的軟體FSlint。FSlint是一個重複文件查找工具，可以使用它來清除不必要的重複文件，筆者經常使用它來釋放Linux系統中的磁碟空間。
  • sql:刪除重複數據並且保留id最小的一條
    當測試資料庫中，有重複數據會導致selectOne
  • EXCEL(WPS表格)——查找、刪除重複
    在收集的信息中，往往會收到重複提交的數據。那麼，怎麼找到這些重複值並刪除它呢？步驟一：查找重複數據選中姓名這一列，在 開始 選項卡中，找到 條件格式——突出顯示單元格規則——重複值。2. 單擊確定後，就可以看到我們的重複值都被標註出來啦！
  • MySQL刪除重複數據
    問：假如有一個student(table_id,name,age)的表，有重複name和age數據, 怎麼寫出delete重複數據的sql？
  • 大神教你在Linux中查找和刪除重複文件的4種方法
    這可能會導致目錄中充斥著各種無用的重複內容。在本教程中，您將學習如何使用rdfind和fdupes命令行工具以及稱為DupeGuru和FSlint的GUI工具在Linux中查找和刪除重複文件。需要注意的是 - 永遠要小心你在系統上刪除的內容，因為這可能會導致不必要的數據丟失。如果您正在使用一個新工具，首先在一個測試目錄中嘗試它，在那裡刪除文件不會出現問題。1.
  • 常用的sql語句集合(適合資料庫初級人員)
    9、創建視圖：create view viewname as select statement刪除視圖：drop view viewname10、幾個基本的sql語句選擇：select * from table1 where 範圍插入：insert into table1(field1,field2) values(value1,
  • SQL中刪除重複數據問題
    ，刪除保留其中一條（是否重複判斷基準為多個欄位）解決方案碰到這樣的問題我們先分解步驟來看創建測試數據找到重複的數據刪除重複的數據並且保留一行創建測試數據我們創建一個人員信息表並在裡面插入一些重複的數據。
  • Excel怎麼快速查找和刪除重複數據
    我們用excel表格記錄了大量的數據，當要做數據整理時候發現很多重複數據，那麼怎麼篩選刪除呢?
  • SQL如何刪除重複數據
    在使用資料庫時，如何刪除重複數據？ 如圖所示：用戶表（user）數據1、輸入查詢語句（查詢name重複數據）select * from user where name in (select name from user group by name having count(name) > 1)
  • EXCEL之VBA-刪除指定列所有非重複項的行,只保留指定條件數據
    注意，直接複製代碼到VBA編輯器可能會有一些多餘的符號，自行查找刪除就可以了。Sub 刪除選擇單元格所在列的非重複項()Dim r As IntegerDim i As IntegerDim c As Integer'------------開始查找並刪除非重複項
  • MySQL如何刪除重複數據
    也就是我們的上面的這個表中每一組重複數據中id最小的一行數據需要保留，其他比較大的id的重複的數據行需要被刪除。當然如果是要保留id行最大的一行數據最為最後的數據行也是可以了，只要在查詢的時候，稍微修改一下SQL語句的min(id)或max(id)函數即可。
  • Mysql sql語句大全
    1、說明：創建資料庫CREATE databasename2、說明：刪除資料庫drop dbname3、說明：備份sql server--- 創建 備份數據的 deviceUSE masterEXEC 'disk''testBack''c:\mssql7backup\MyNwind_1.dat
  • SQL語句大全,所有的SQL都在這裡
    當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重複行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。 B：EXCEPT 運算符 EXCEPT運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重複行而派生出一個結果表。
  • SQL 資料庫語句
    ,value2)刪除：delete from table1 where 範圍更新：update table1 set field1=value1 where 範圍查找：select * from table1 where field1 like 』%value1%』 ---like的語法很精妙，查資料!
  • 新鮮出爐的SQL語句大全
    當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重複行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。B：EXCEPT運算符EXCEPT運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重複行而派生出一個結果表。
  • SQL語句解決重複數據並進行單一入庫問題,你值得了解
    01問題描述：我們這裡是做了一個數據的接入問題，需要不停的向資料庫中寫入數據，那這些數據中有的是重複的，重複的數據寫入資料庫既沒有意義，也浪費內存空間，所以我們需要想一個辦法來解決數據重複插入的問題。>解決方案：這裡有三個解決方法：1、利用ignore關鍵字方法原理：忽略了有唯一索引欄位的相同數據，只添加不同的數據，成功的操作條數是添加的真實條數，若沒有ignore關鍵字會報錯sql
  • 利用VBA代碼刪除空白行,如何刪除重複數據
    大家好，今日內容仍是和大家分享VBA編程中常用的簡單「積木」過程代碼，NO.136-NO.138，內容是：插入多行表格的方法、如何刪除工作表中的空白行、刪除重複數據行等內容。使用工作表CountA函數判斷當前行已使用單元格的數量，如果為零說明此行是空行則使用Delete刪除。 UsedRange屬性應用於Worksheet對象，返回指定工作表中已使用區域的Range對象，即返回工作表中已使用的單元格區域。
  • 如何實現 MySQL 中通過SQL語句刪除重複記錄並且只保留一條記錄
    最近在做題庫系統，由於在題庫中添加了重複的試題，所以需要查詢出重複的試題，並且刪除掉重複的試題只保留其中
  • Excel中如何刪除重複行(二)
    第2種情況：行與行中，某列內容重複，其他列內容可能不一致 　　此時我們希望以某列為條件，找出重複的內容，把其所在的整行刪除。如下圖所示： 　　我們只需要A列（即：姓名列）沒有重複。 　　操作方法： 　　1.添加新列E，輸入數字等差序列（如1、2、3……），拖動手動柄就可以。 　　2.將全表按A列排序。