删除重复行但保留唯一副本

时间：2021-07-05 16:27:44

I got a SQL table of the form:-

我有一个表格的SQL表: - 

Name     Age
Kim       5
Tom       8
Jim       12
Kim       5
David     21
Jim       12

In the above scenario, Kim and Jim are duplicated and they exist twice.

在上面的场景中,Kim和Jim是重复的,它们存在两次。

If I have a large table of the above form, with about 60000 entries, how do I write a sql query to delete only the duplicates but still retain the unique ones?

如果我有一个上面表格的大表,有大约60000个条目,我如何编写一个sql查询只删除重复项但仍然保留唯一的?

1 个解决方案

#1

3  

With the help of Common Table Expression and a Window Function, you can easily delete duplicate records.

借助Common Table Expression和Window Function,您可以轻松删除重复记录。

WITH dups
AS
(
    SELECT  Name, Age,
            ROW_NUMBER() OVER (PARTITION BY Name, Age ORDER BY Age DESC) rn
    FROM    TableName
)
DELETE FROM dups
WHERE   rn > 1

#1

3  

With the help of Common Table Expression and a Window Function, you can easily delete duplicate records.

借助Common Table Expression和Window Function,您可以轻松删除重复记录。

WITH dups
AS
(
    SELECT  Name, Age,
            ROW_NUMBER() OVER (PARTITION BY Name, Age ORDER BY Age DESC) rn
    FROM    TableName
)
DELETE FROM dups
WHERE   rn > 1
标签：

相关文章