如何在SQL Server 2008中删除重复行？

Question

如何在 SQL Server 2008 中删除重复行？

- Gold

---

通过在数据库中设置唯一性约束来避免它们。 - Jherico

如果你没有主键，那么基本上你就没有表。没有主键意味着无法唯一标识你的行。添加一个主键 - 然后你可以开始解决其他问题了。 - marc_s

Answer 1

使用公共表达式（CTE）是最简单的方法。当我有原始数据需要导入时，我会使用这种方法来清理数据，首先确保没有重复项，即每行都有某种唯一标识。

WITH numbered AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY [dupe-column-list] ORDER BY [dupe-column-list]) AS _dupe_num FROM [table-name] WHERE 1=1
)
DELETE FROM numbered WHERE _dupe_num > 1;

"dupe-column-list" 部分是您列出所有涉及的列，其中希望值是唯一的。 ORDER BY 是在一组重复数据中决定哪一行“获胜”和哪一行被删除的地方。（ "WHERE 1 = 1" 只是个人习惯。）

它能够正常工作的原因是因为Sql Server会保留选定CTE中每个源行的内部、唯一引用。因此，在执行 DELETE 操作时，它知道要删除的确切行，无论您在 CTE 的 select-list 中放入什么内容。（如果您很紧张，您可以将 “DELETE” 更改为 “SELECT *”，但由于您具有重复行，这并没有帮助；如果您能够唯一地识别每一行，那么您就不会阅读此内容了。）

示例：

CREATE TABLE ##_dupes (col1 int, col2 int, col3 varchar(50));
INSERT INTO ##_dupes 
    VALUES (1, 1, 'one,one')
        , (2, 2, 'two,two')
        , (3, 3, 'three,three')
        , (1, 1, 'one,one')
        , (1, 2, 'one,two')
        , (3, 3, 'three,three')
        , (1, 1, 'one,one')
        , (1, 2, '1,2');

在这8行数据中，有5行存在重复问题；需要移除其中的3行。这样做可能会带来以下问题：

SELECT col1
    , col2
    , col3
    , COUNT(1) AS _total 
    FROM ##_dupes 
    WHERE 1=1 
    GROUP BY col1, col2, col3
    HAVING COUNT(1) > 1
    ORDER BY _total DESC;

现在运行以下查询以去除重复项，并从每组重复项中保留1行。

WITH numbered AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY col1, col2, col3 ORDER BY col1, col2, col3) AS _dupe_num FROM ##_dupes WHERE 1=1
)
DELETE FROM numbered WHERE _dupe_num > 1;

现在你只剩下5行，这些行没有重复。

- Granger

Answer 2

添加主键。每个表都应该有一个主键。它可以是identity，你可以忽略它，但请确保每个表都定义了主键。

想象一下你有一个这样的表：

create table T (
    id int identity,
    colA varchar(30) not null,
    colB varchar(30) not null
)

然后你可以说类似这样的话：

delete T
from T t1
where exists
(select null from T t2
where t2.colA = t1.colA
and t2.colB = t1.colB
and t2.id <> t1.id)

另一个技巧是选择具有最小id的不同记录，并保留它们：

delete T
where id not in
(select min(id) from T
group by colA, colB)

（抱歉，我没有测试过这些方法，但其中一个可能会帮助你找到解决方案。）

请注意，如果您没有主键，唯一的其他方法是利用伪列，例如ROWID - 但我不确定 SQL Server 2008 是否提供了这个想法。

- AWhitford

Answer 3

即使您没有主键，也可以使用以下代码删除重复数据。

delete from (Tablename)
          where tablename.%%physloc%%
          NOT IN (select MIN(b.%%physloc%%)
          from tablename b
          group by b.Column1,b.column2,b.column3
          );

- Yugndhar

---

+1 但是你应该提到 physloc 功能的“未记录”性质 - 尽管它可能会在未来几个版本中保留，但永远不知道！ - Our Man in Bananas

Answer 4

假设您有一个名为id的主键和其他列为col2 ...coln，并且通过“重复”行，您指的是除PK之外的所有列值都重复的所有行。

delete from A where id not in
(select min(id) from A
group by col2, col3, ...coln) as x

即按照所有非主键列进行分组

- davek

---

它不能工作，因为我没有主键。 我如何在没有主键的情况下完成它？ - Gold