在表中删除多个重复行

Question

我在一个表格中有多个重复的组（一个有3条记录，另一个有2条等）-存在多行，其中超过1个。

以下是我想到的删除它们的方法，但我必须运行脚本直到没有重复为止：

set rowcount 1
delete from Table
where code in (
  select code from Table 
  group by code
  having (count(code) > 1)
)
set rowcount 0

这在一定程度上很有效。我需要对每组重复运行此操作，然后它只会删除1个（这就是我现在所需的全部内容）。

- Dan

---

你想保留哪个重复项 -- 第一个/最低的还是最新/最大的？并且针对哪个版本的SQL Server？ - OMG Ponies

任何一个最大的ID都可以。SQL Server 2000（是的，我知道）。 - Dan

可能是重复的问题：SQL - 如何删除重复行？ - Martin Smith

谢谢链接，马丁 - 我担心这可能是一个重复的问题。 - Dan

Answer 1

如果您的表上有一个关键列，那么您可以使用它来唯一地识别表中的“不同”行。只需使用子查询来识别唯一行的ID列表，然后删除此集合之外的所有内容。大致如下...

create table #TempTable
(
    ID int identity(1,1) not null primary key,
    SomeData varchar(100) not null
)

insert into #TempTable(SomeData) values('someData1')
insert into #TempTable(SomeData) values('someData1')
insert into #TempTable(SomeData) values('someData2')
insert into #TempTable(SomeData) values('someData2')
insert into #TempTable(SomeData) values('someData2')
insert into #TempTable(SomeData) values('someData3')
insert into #TempTable(SomeData) values('someData4')

select * from #TempTable

--Records to be deleted
SELECT ID
FROM #TempTable
WHERE ID NOT IN
(
    select MAX(ID)
    from #TempTable
    group by SomeData
)

--Delete them
DELETE
FROM #TempTable
WHERE ID NOT IN
(
    select MAX(ID)
    from #TempTable
    group by SomeData
)

--Final Result Set
select * from #TempTable

drop table #TempTable;

你也可以使用CTE（公共表达式）作为例子：

WITH UniqueRecords AS
(
    select MAX(ID) AS ID
    from #TempTable
    group by SomeData
)
DELETE A
FROM #TempTable A
    LEFT outer join UniqueRecords B on
        A.ID = B.ID
WHERE B.ID IS NULL

- John Sansom

---

刚刚注意到原帖是关于 SQL Server 2000 的，但是可以使用 CTE 更高效地完成此操作，参见这里：https://dev59.com/dXVD5IYBdhLWcg3wU56H#3822833 - Martin Smith

Answer 2

将唯一行复制到临时表中通常更有效，
删除源表，将临时表重命名为原始表。

我重用了＃TempTable的定义和数据，在此称为SrcTable，因为无法将临时表重命名为常规表）

create table SrcTable
(
    ID int identity(1,1) not null primary key,
    SomeData varchar(100) not null
)

insert into SrcTable(SomeData) values('someData1')
insert into SrcTable(SomeData) values('someData1')
insert into SrcTable(SomeData) values('someData2')
insert into SrcTable(SomeData) values('someData2')
insert into SrcTable(SomeData) values('someData2')
insert into SrcTable(SomeData) values('someData3')
insert into SrcTable(SomeData) values('someData4')

此前的回答由 John Sansom 提供，链接在此

-- cloning "unique" part
SELECT * INTO TempTable 
FROM SrcTable --original table
WHERE id IN  
(SELECT MAX(id) AS ID
FROM SrcTable
GROUP BY SomeData);
GO;

DROP TABLE SrcTable
GO;

sys.sp_rename 'TempTable', 'SrcTable'

- Gennady Vanin Геннадий Ванин

---

如果您使用此策略，在重命名后应重新添加键、约束、标识值、索引和关系。 - pomarc

Answer 3

你也可以使用ROW_NUMBER()函数来过滤重复项

;WITH [CTE_DUPLICATES] AS 
(
SELECT RN = ROW_NUMBER() OVER (PARTITION BY SomeData ORDER BY SomeData)
FROM #TempTable
) 
DELETE FROM [CTE_DUPLICATES] WHERE RN > 1

- anivas

---

这个问题的 OP 是在 SQL Server 2000 上。 - Martin Smith

Answer 4

SET ROWCOUNT 1    
DELETE Table    
FROM Table a    
WHERE (SELECT COUNT(*) FROM Table b WHERE b.Code = a.Code ) > 1    
WHILE @@rowcount > 0    
  DELETE Table    
  FROM Table a    
  WHERE (SELECT COUNT(*) FROM Table b WHERE b.Code = a.Code ) > 1    
SET ROWCOUNT 0

这将删除所有重复行，但如果您想根据属性进行比较，可以添加属性。

- Azhar