Tìm bản ghi trùng trên bảng

Cách tìm và xóa bản ghi bị trùng trên bảng

TÌM CÁC BẢN GHI

Cách 1:

SELECT a.*
FROM users a
JOIN (SELECT username, email, COUNT(*)
FROM users 
GROUP BY username, email
HAVING count(*) > 1 ) b
ON a.username = b.username
AND a.email = b.email
ORDER BY a.email

Cách 2:

WITH customerdata AS (
    SELECT 
        CustomerID,
        FirstName, 
        LastName, 
        CompanyName,
        ROW_NUMBER() OVER (
        PARTITION BY FirstName,LastName
        ORDER BY FirstName, LastName
            ) As Occurrences
    FROM 
        [SalesLT].[Customer]
) 
SELECT 
  * 
FROM 
    customerdata 
WHERE 
    Occurrences > 1;

XOÁ CÁC BẢN GHI

Cách 1:

DELETE FROM [SampleDB].[dbo].[Employee]
    WHERE ID NOT IN
    (
        SELECT MAX(ID) AS MaxRecordID
        FROM [SampleDB].[dbo].[Employee]
        GROUP BY [FirstName], 
                 [LastName], 
                 [Country]
    );

Cách 2:

WITH CTE([FirstName], 
    [LastName], 
    [Country], 
    DuplicateCount)
AS (SELECT [FirstName], 
           [LastName], 
           [Country], 
           ROW_NUMBER() OVER(PARTITION BY [FirstName], 
                                          [LastName], 
                                          [Country]
           ORDER BY ID) AS DuplicateCount
    FROM [SampleDB].[dbo].[Employee])
DELETE FROM CTE
WHERE DuplicateCount > 1;


--Hoặc dùng rank

DELETE E
    FROM [SampleDB].[dbo].[Employee] E
         INNER JOIN
    (
        SELECT *, 
               RANK() OVER(PARTITION BY firstname, 
                                        lastname, 
                                        country
               ORDER BY id) rank
        FROM [SampleDB].[dbo].[Employee]
    ) T ON E.ID = t.ID
    WHERE rank > 1;

Different ways to SQL delete duplicate rows from a SQL TableSQL Shack - articles about database auditing, server performance, data recovery, and more

How To Delete Duplicate Rows From A Table In SQL ServerC# Corner