Chúng ta có một bảng:

ID    NAME    EMAIL
1     John    asd@asd.com
2     Sam     asd@asd.com
3     Tom     asd@asd.com
4     Bob     bob@asd.com
5     Tom     asd@asd.com

làm thế nào để xóa các trùng lắp khỏi bảng trên (có cùng Name và Email)?

Giải pháp:

DELETE FROM users
WHERE id IN (
   SELECT id/*, name, email*/
   FROM users u, users u2
   WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
)

hoặc sử dụng PARTITION BY:

DELETE d
   FROM @YourTable d
      INNER JOIN (SELECT
         y.id,y.name,y.email,ROW_NUMBER() OVER(PARTITION BY y.name,y.email ORDER BY
y.name,y.email,y.id) AS RowRank
            FROM @YourTable y
               INNER JOIN (SELECT
                 name,email, COUNT(*) AS CountOf
                 FROM @YourTable
                 GROUP BY name,email
                 HAVING COUNT(*)>1
                ) dt ON y.name=dt.name AND y.email=dt.email
             ) dt2 ON d.id=dt2.id
      WHERE dt2.RowRank!=1
SELECT * FROM @YourTable