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

Vũ Huy Tâm

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:

1
2
3
4
5
6
7
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.




Tags: , , , , , , , ,

7 Comments
Posted on 3/5/2010 | Categories: SQL Server Programming

Các bài viết tương tự

Comments
  • Red Devilic (03/05/2010 7:29 pm)

    Hình như font của bác bị lỗi. Lúc hiển thị lên toàn kí tự lạ :)

    Một cách khác có thể xóa luôn các bản ghi trùng mà ko cần dùng bảng tạm như sau:

    Với trường hợp bảng của bác có IDENTITY, hoặc có PRIMARY KEY

    DELETE FROM DiaChiEmail A

    WHERE ID NOT IN (SELECT MAX(ID) FROM DiaChiEmail GROUP BY EMail)

    Trường hợp bảng không có IDENTITY thì có thể dùng ROW_NUMBER (SQL 2k5) hoặc câu lệnh lồng (SQL 2000)

    Tuy nhiên cách của bác dễ hiểu và trực quan hơn

  • Khoa Nguyễn Gia (04/05/2010 8:07 am)

    Lần đầu tiên mình thấy cái viết Delete join thế này

    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

    Vậy là chỉ giữ lại ID nhỏ nhất, tức là insert đầu tiên.

    Còn cách này

    DELETE FROM DiaChiEmail A

    WHERE ID NOT IN (SELECT MAX(ID) FROM DiaChiEmail GROUP BY EMail)

    của Red Devilic thì giữ lại insert cuối cùng.

  • vuht2000 (04/05/2010 8:42 am)

    @Red Devilic: Cảm ơn bạn đã chỉ ra lỗi font, tôi đã sửa lại rồi. Cách làm của bạn rất thông minh, code viết rất gọn. Tuy nhiên tôi chỉ có băn khoăn nho nhỏ là câu lệnh con sẽ trả về tất cả các unique email, so với cách trong bài viết chỉ trả về các email bị trùng. Ví dụ nếu bảng có 1 triệu email trong đó có 200K email bị lặp lại 1 lần (count=2), thì câu lệnh con của bạn sẽ gồm 800K unique email, trong khi câu lệnh con trong bài viết sẽ chỉ gồm 200K email bị lặp. Mức độ ảnh hưởng thế nào thì tôi cũng chưa rõ, nhưng tôi thích cách làm của bạn.

    @Khoa Nguyễn Gia: Lệnh DELETE trên sẽ xóa đi các ID nhỏ hơn MAX(ID), như vậy là nó giữ lại ID lớn nhất. Cách làm của bạn Red Devilic cũng cho kết quả tương tự như vậy

  • Nga (22/02/2012 4:30 am)

    Loại bỏ bảng ghi trùng này áp dụng cho ID là kiểu số. Vậy nếu muốn xoá trùng cho ID kiểu chuổi thì phải làm sao? Dùng hàm Max() cho kiểu chuỗi thì ko được, theo e được biết là vậy. Xin các bác chỉ giúp e.

    Cám ơn!

    • Vũ Huy Tâm (22/02/2012 6:18 pm)

      hàm MAX() cũng áp dụng được với chuỗi bạn ạ.

      • Nga (23/02/2012 11:24 pm)

        Vậy điều kiện a.ID < b.ID khi ID là kiểu số, còn khi là kiểu chuỗi thì mình đâu có thể so sánh vậy được phải không bác Tâm? So sánh 2 ID là kiểu chuỗi sẽ phức tạp hơn nhiều phải không bác Tâm.

      • Vũ Huy Tâm (24/02/2012 2:50 pm)

        Nếu bảng không có trường ID kiểu số, bạn có thể add nó vào bảng (dùng IDENTITY), thực hiện loại bỏ trùng xong thì xóa trường đó đi

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>