sql - 删除重复行

Question

我有一个看起来像这样的表

Table1

Id, Name

我如何编写一个查询来删除所有具有重复名称的行，但保留具有较低 ID 的行？

Answer 1

如果您使用的是 SQL Server 2005 或更高版本:

With Dups As
    (
    Select Id, Name
        , Row_Number() Over ( Partition By Name Order By Id ) As Num
    From Table1
    )
Delete Table1
Where Id In (
            Select Id
            From Dups
            Where Num > 1
            )

如果使用 SQL Server 2000 及更早版本

Delete Table1
Where Exists    (
                Select 1
                From Table1 As T1
                Where T1.Name = Table1.Name
                Having Min( T1.Id ) <> Table1.Id
                )