刪除重複行的SQL查詢

---

在使用資料庫避免重複資料時，我們應該在建立資料庫表時遵循某些實踐。

• 定義主鍵以識別行簇和非簇索引。
• 使用約束來提高資料完整性和效能。

即使遵循最佳實踐，資料庫表也可能包含重複行。這些重複行在從資料庫檢索資料時會造成問題。

因此，我們必須確保資料庫行唯一。為此，首先我們需要驗證表中是否存在重複行，如果存在重複行，則必須透過更改表資料來刪除它們。本文將討論從表中刪除重複行的不同方法。

• 使用GroupBy和Having子句
• 使用公用表表達式 (CTE)
• 使用RANK()函式

建立資料庫表

首先，讓我們使用CREATE TABLE語句在SQL Server中建立一個名為Author的表。

CREATE TABLE Author(
   [ID] INT identity(1,1),
   [FirstName] Varchar(100),
   [LastName] Varchar(100),
   [Country] Varchar(100)
)

現在，讓我們向Author表中插入值。

INSERT INTO Author([FirstName], [LastName], [Country]) values('Mithlesh', 'Upadhyay', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Mithlesh', 'Upadhyay', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Mithlesh', 'Upadhyay', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Rudra', 'Upadhyay', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Rudra', 'Upadhyay', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Chandan', 'Gautam', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Chandan', 'Gautam', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Chandan', 'Gautam', 'India')

這些值將如下所示插入到Author表中。我們可以使用以下查詢檢查此Author表及其行：

SELECT * FROM Author

輸出將如下所示。請注意，該表包含如下所示的重複行：

ID	FirstName	LastName	Country
1	Mithlesh	Upadhyay	India
2	Mithlesh	Upadhyay	India
3	Mithlesh	Upadhyay	India
4	Rudra	Upadhyay	India
5	Rudra	Upadhyay	India
6	Chandan	Gautam	India
7	Chandan	Gautam	India
8	Chandan	Gautam	India

現在，我們將編寫不同的查詢以從上面的Author表中刪除重複行。

使用GroupBy和Having子句

我們可以使用GROUP BY子句識別重複行。它根據列對行進行分組。我們將選擇FirstName、LastName和Country來分組行，然後從每個組中選擇一行。它將返回Author表中的唯一行。

DELETE FROM Author
WHERE ID NOT IN (
   SELECT MIN(ID)
   FROM Author
   GROUP BY FirstName, LastName, Country
   HAVING COUNT(*) > 1
)

上述查詢後Author表的內容。請注意，由於重複的值，行號2、3、5、7和8被刪除了。

ID	FirstName	LastName	Country
1	Mithlesh	Upadhyay	India
4	Rudra	Upadhyay	India
6	Chandan	Gautam	India

使用公用表表達式 (CTE)

我們還可以使用公用表表達式 (CTE) 來刪除SQL Server中的重複行。CTE是在SQL Server 2005中引入的。我們將編寫SQL ROW_NUMBER()函式。它為行新增一個唯一的順序行號。我們使用Partition by子句使用FirstName、LastName和Country為每一行生成一個行號。

請注意，我們有初始的包含重複行的表，因此我們可以應用此SQL來刪除重複行並返回原始表作為輸出。

WITH CTE AS (
   SELECT ID, FirstName, LastName, Country, ROW_NUMBER() 
   OVER (PARTITION BY FirstName, LastName, Country ORDER BY ID) 
   AS row_num FROM Author
)
SELECT * FROM CTE

它將新增另一列“row_num”。此列將顯示重複行出現的次數，如下所示：

ID	FirstName	LastName	Country	row_num
6	Chandan	Gautam	India	1
1	Mithlesh	Upadhyay	India	1
4	Rudra	Upadhyay	India	1

現在，我們將刪除所有row_num大於1的行。因此，它將保留唯一行。您可以使用以下查詢執行此操作：

WITH CTE AS ( 
   SELECT ID, FirstName, LastName, Country, ROW_NUMBER() 
   OVER (PARTITION BY FirstName, LastName, Country ORDER BY ID) 
   AS row_num FROM Author
)
DELETE FROM Author WHERE ID IN ( 
   SELECT ID FROM CTE WHERE row_num > 1
)

它將從Author表中刪除所有重複行。您可以使用以下查詢檢查此更新後的表：

SELECT * FROM Author

輸出將是：

ID	FirstName	LastName	Country
1	Mithlesh	Upadhyay	India
4	Rudra	Upadhyay	India
6	Chandan	Gautam	India

使用RANK()函式

我們還可以使用RANK函式刪除SQL中的重複行。此方法類似於上面使用ROW_NUMBER()的方法。RANK()函式顯示重複行的值。

請注意，我們有初始的包含重複行的表，因此我們可以應用此SQL來刪除重複行並返回原始表作為輸出。

WITH CTE AS ( 
   SELECT ID, FirstName, LastName, Country, RANK() 
   OVER (PARTITION BY FirstName, LastName, Country ORDER BY ID) 
   AS rank_num FROM Author
)
SELECT * FROM CTE

請注意，這與上述方法相同，但此處我們使用RANK()代替ROW_NUMBER()函式，因此列名為rank_num而不是row_num。從Author表中刪除重複行的最終查詢是：

WITH CTE AS ( 
   SELECT ID, FirstName, LastName, Country, RANK() 
   OVER (PARTITION BY FirstName, LastName, Country ORDER BY ID) 
   AS rank_num FROM Author
)
DELETE FROM Author WHERE ID IN ( 
   SELECT ID FROM CTE WHERE rank_num > 1
)

它將從Author表中刪除所有重複行。上述查詢後Author表的內容如下：

ID	FirstName	LastName	Country
1	Mithlesh	Upadhyay	India
4	Rudra	Upadhyay	India
6	Chandan	Gautam	India

結論

在本文中，我們討論了三種從SQL資料庫表中刪除重複行的不同方法。我們在第一種方法中使用group by和having子句。

第一種方法在SQL中編寫起來最簡單。我們在第二種和第三種方法中分別使用了ROW_NUMBER()和RANK()函式。最後兩種方法彼此相似，因此您可以很容易地理解它們。

常見問題 (FAQ)

為什麼要從表中刪除重複行？

我們從表中刪除重複行，因為重複行會造成資料完整性問題。具有唯一行的表始終確保資料的完整性、效能和查詢的計算。

在SQL中使用ROW_NUMBER()和RANK()函式有什麼區別？

ROW_NUMBER()和RANK()函式都在分割槽內為行分配數字。ROW_NUMBER()為每一行分配一個唯一的數字。而RANK()為相同的行分配相同的排名，並跳過後續的數字。

何時使用GROUP BY和HAVING刪除重複項而不是CTE？

區別不大，但這種方法更容易編寫和理解。