iT邦幫忙

DAY 18
8

T-SQL 應用之美系列 第 18

如何使用次序函數刪除重複的記錄

次序函數並不是只用於查詢資料時,針對某個欄位進行排名。在實務應用上,當資料表沒有唯一的一個識別編號欄位時,可以用來取代 Cursor 或暫存資料表,以便刪除重複的資料記錄。
在匯入資料時,最怕的就是匯入了重複的記錄。在 Microsoft Server 2005 之前,為了避免發生這種情形(這是指當資料表沒有唯一的一個識別編號欄位的時候),常用的方法就是使用 Cursor 、暫存資料表或是 SELECT...GROUP BY...HAVING...,這些作法在效能上當然不會太好(請參考「詢問重複資料處理」,人家實際使用的效能差異說明)。

透過 Microsoft Server 2005 全新提供的次序函數:ROW_NUMBER() 與一般資料表運算式(Common Table Expressions,CTE),就可以輕鬆刪除重複的資料。

先從 AdventureWorks 資料庫中的 HumanResources.Employee 資料表裡,查詢資料來建立年齡欄位會有重複的測試用資料表:

USE AdventureWorks
GO

-- 使用 SELECT...INTO 匯入資料
SELECT 年齡 = dbo.fn_GetAge(BirthDate),
	性別 = CASE Gender
			WHEN 'M' THEN N'男'
			WHEN 'F' THEN N'女'
		END,
	婚姻 = CASE MaritalStatus
			WHEN 'S' THEN N'單身'
			WHEN 'M' THEN N'已婚'
		END
INTO 測試資料表
FROM HumanResources.Employee

-- 看一下匯入的資料
SELECT * FROM 測試資料表 ORDER BY 年齡

執行結果:

附註:
建立 fn_GetAge() 函數的說明,請參考先前分享的文章:「<a href="">如何使用次序函數於查詢資料時,針對某個欄位進行排名</a>」

現在,先使用「<a href="">如何使用次序函數於查詢資料時,針對某個欄位進行排名</a>」討論過的 ROW_NUMBER() 函數,依照年齡、性別與婚姻由小至大,產生不會重複且是唯一的編號欄位:

SELECT 編號 = ROW_NUMBER() OVER (ORDER BY 年齡, 性別, 婚姻), *
	FROM 測試資料表

執行結果的部分畫面:

接著,我們先用一般資料表運算式(Common Table Expressions,CTE),透過 GROUP BY 子句找出重複記錄中的第一筆資料:

WITH 重複的記錄 AS
	(SELECT 編號 = ROW_NUMBER() OVER (ORDER BY 年齡, 性別, 婚姻), *
		FROM 測試資料表)
SELECT * FROM 重複的記錄
WHERE 編號 NOT IN
(SELECT MIN(編號) FROM 重複的記錄
	GROUP BY 年齡, 性別, 婚姻)

執行結果的部分畫面:

既然已經找出重複的資料,當然就可以刪除這些重複的資料,只是現在要把剛剛那段程式碼中的 SELECT *,換成 DELETE:

WITH 重複的記錄 AS
	(SELECT 編號 = ROW_NUMBER() OVER (ORDER BY 年齡, 性別, 婚姻), *
		FROM 測試資料表)
DELETE * FROM 重複的記錄
WHERE 編號 NOT IN
(SELECT MIN(編號) FROM 重複的記錄
	GROUP BY 年齡, 性別, 婚姻)

執行結果:

用下面的程式碼查詢刪除重複資料後的結果:

SELECT * FROM 測試資料表 ORDER BY 年齡

比較一下刪除重複資料前後的部分畫面(左邊是刪除前,右邊是刪除後):


上一篇
如何使用次序函數於查詢資料時,針對某個欄位進行排名(下)
下一篇
在 SELECT 中,使用萬用字元:*
系列文
T-SQL 應用之美30

尚未有邦友留言

立即登入留言