必備神技能 ,MySQL 查詢並刪除重複行

語言: CN / TW / HK

點選進入“PHP開源社群”    

免費獲取進階面試、文件、影片資源

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

如何查詢重複行

第一步是定義什麼樣的行才是重複行。多數情況下很簡單:它們 某一列 具有相同的值。本文采用這一定義,或許你對“重複”的定義比這複雜,你需要對sql做些修改。 本文要用到的資料樣本:

create table test(id int not primary keyday date not null);  
 
insert into test(iddayvalues(1'2006-10-08');  
insert into test(iddayvalues(2'2006-10-08');  
insert into test(iddayvalues(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 dayMIN(idfrom 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 (11);  
insert into a_b_c(b,c) values (12);  
insert into a_b_c(b,c) values (13);  
insert into a_b_c(b,c) values (21);  
insert into a_b_c(b,c) values (22);  
insert into a_b_c(b,c) values (23);  
insert into a_b_c(b,c) values (31);  
insert into a_b_c(b,c) values (32);  
insert into a_b_c(b,c) values (33);

現在,你可以輕易看到表裡面有一些重複的行,但找不到兩行具有相同的二元組{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欄位排序,看看它是如何分組的

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

幾種正確的方法

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

select b as valuecount(*) as cnt, 'b' as what_col  
from a_b_c group by b having count(*) > 1  
union  
select c as valuecount(*) 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能用,我想會是最簡單不過的了。

原文連結:https://blog.csdn.net/zhengzhb/article/details/8590390

END

PHP開源社群

掃描關注  進入”PHP資料“

免費獲取進階

面試、文件、影片資源