Loại Bỏ Bản Ghi Trùng Trong Bảng

Khi làm việc với dữ liệu, có thể bạn gặp những tình huống trong đó, dữ liệu trong bảng có những bản ghi trùng nhau ở những trường đáng nhẽ ra không được trùng. Ví dụ bạn có 1 bảng chứa danh sách các địa chỉ email tên là DiaChiEmail gồm có các trường: ID, Email, và Ten; dữ liệu này được dùng cho công tác marketing (nôm na là spam

). Theo logic của dữ liệu thì trường Email xác định duy nhất một địa chỉ email, và bạn chỉ muốn gửi 1 email tới mỗi địa chỉ. Vì những lý do nào đó (có thể do lỗi nhập dữ liệu, hoặc khi import dữ liệu từ nhiều nguồn khác nhau…), bảng DiaChiEmail lại xuất hiện các bản ghi có trường Email giống nhau, mặc dù các trường còn lại thì khác nhau. Nay bạn muốn loại bỏ các bản ghi trùng và chỉ giữ lại một bản ghi duy nhất cho mỗi Email, và giả sử bạn muốn giữ lại bản ghi có trường ID lớn nhất (vì bản ghi này có dữ liệu mới nhất). Ta hãy xem xét cách làm như thế nào qua một ví dụ cụ thể:

-- tạo bảng
CREATE TABLE dbo.DiaChiEmail(
ID INT IDENTITY PRIMARY KEY,
Email VARCHAR(100),
Ten NVARCHAR(100)
)
GO
-- insert một vài bản ghi
INSERT dbo.DiaChiEmail(
Email,
Ten
)
SELECT 'abc@gmail.com', 'abc' UNION ALL -- #1
SELECT 'def@yahoo.com', 'def' UNION ALL -- #2
SELECT 'ghi@hotmail.com', 'ghi' UNION ALL -- #3
SELECT 'jkl@ymail.com', 'jkl' UNION ALL -- #4
SELECT 'abc@gmail.com', 'abc 2' UNION ALL -- #5, trùng với bản ghi #1
SELECT 'abc@gmail.com', 'abc 3' UNION ALL -- #6, trùng với bản ghi #1
SELECT 'ghi@hotmail.com', 'ghi 2' -- #7, trùng với bản ghi #3

Như vậy địa chỉ email abc@gmail xuất hiện 3 lần với tên khác nhau; còn ghi@hotmail.com xuất hiện 2 lần. Vì ta chỉ muốn giữ lại bản ghi sau cùng cho mỗi địa chỉ email, kết quả mà ta mong đợi từ quá trình loại bỏ bản ghi trùng sẽ là:

‘def@yahoo.com’, ‘def’, ’2010/01/13′ — bản ghi #2
‘jkl@ymail.com’, ‘jkl’, ’2010/01/14′ — bản ghi 4
‘abc@gmail.com’, ‘abc 3′, ’2010/01/15′ — bản ghi #6
‘ghi@hotmail.com’, ‘ghi 2′, ’2010/01/16′ — bản ghi #7
Câu lệnh sau giúp ta làm điều đó:

DELETE a
FROM DiaChiEmail a
JOIN
(
SELECT Email, MAX(ID) AS ID
FROM DiaChiEmail
GROUP BY Email
HAVING COUNT(*)>1
) b
ON a.Email = b.Email
AND a.ID < b.ID

Giải thích: Câu lệnh con từ dòng 5-8 lấy ra các bản ghi mới nhất cho mỗi email bị trùng. Mệnh đề HAVING COUNT(*)>1 đảm bảo các bản ghi có email xuất hiện một lần không bị động đến. Ở ví dụ trên, câu lệnh con sẽ chứa các bản ghi #6 và #7. Sau đó lệnh DELETE xóa đi trong bảng chính các bản ghi “cũ” hơn (có ID nhỏ hơn giá trị lớn nhất) cho các email bị trùng. Đó là các bản ghi #1, #5 và #3.

Trong trường hợp bảng DiaChiEmail có rất nhiều bản ghi và số bản ghi bị trùng cũng rất lớn, bạn có thể đưa kết quả của câu lệnh con ở trên vào một bảng tạm, sau đó tạo index trên trường Email của bảng tạm để hỗ trợ câu lệnh DELETE:

CREATE TABLE #BanGhiTrung(
Email VARCHAR(100) PRIMARY KEY,
ID INT
)
GO
INSERT #BanGhiTrung(Email, ID)
SELECT Email, MAX(ID) AS ID
FROM dbo.DiaChiEmail
GROUP BY Email
HAVING COUNT(*) > 1
GO
DELETE a
FROM DiaChiEmail a
JOIN #BanGhiTrung b
ON a.Email = b.Email
AND a.ID < b.ID

<== Bổ sung ngày 05/06/2010 ==>

Bạn Red Devilic cung cấp một cách làm dùng Common Table Expression kết hợp với hàm ROW_NUMBER(), là những tính năng mới trong version 2005 trở lên:

WITH [TBL_DUPLICATE] AS
(
SELECT
  STT = ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID DESC), Email
FROM DiaChiEmail
)
DELETE FROM [TBL_DUPLICATE] WHERE STT > 1

Giải thích: Đoạn lệnh từ dòng 3-5 trả về 1 Common Table Expression gồm có Email và STT cho mỗi Email; bắt đầu từ 1 và tăng dần cho mỗi Email bị trùng (“ORDER BY ID DESC”) – chức năng của PARTITION gần giống như GROUP BY. Các bản ghi không bị trùng thì tất nhiên chỉ xuất hiện 1 lần và có STT=1. Sau đó lệnh DELETE áp dụng trên Common Table Expression để xóa đi các bản ghi có STT>1, tức là chỉ giữ lại Email có ID lớn nhất. Các Email không bị trùng vì thế không bị ảnh hưởng. Lệnh DELETE này có tác dụng trên bảng và do đó các bản ghi có Email bị trùng bị xóa khỏi bảng.

tham khảo tại đây

24 thg 10, 2015

Related Articles :

NEW PICTURE

POPULAR POST