首页 / 知识

关于sql：如何删除表中的重复行

2023-04-17 02:28:00

How can I delete duplicate rows in a table

我有一张桌子，上面说三列。没有主键，因此可以有重复的行。我只需要保留一个，然后删除其他。任何想法如何做到这一点是Sql Server？

我将选择DISTINCT这些行，并将其放入临时表中，然后删除源表并从临时表中复制回数据。
编辑：现在带有代码片段！

1
2
3
4
5
6
7
8

INSERT INTO TABLE_2
SELECT DISTINCT * FROM TABLE_1
GO
DELETE FROM TABLE_1
GO
INSERT INTO TABLE_1
SELECT * FROM TABLE_2
GO

当您的PK只是所有表列的一个子集时，以下示例同样适用。

(注意：我更喜欢插入另一个代理id列的方法。但是也许此解决方案也很方便。)

首先找到重复的行：

1
2
3
4

SELECT col1, col2, COUNT(*)
FROM t1
GROUP BY col1, col2
HAVING COUNT(*) > 1

如果只有几个，则可以手动删除它们：

1
2
3

SET rowcount 1
DELETE FROM t1
WHERE col1=1 AND col2=1

" rowcount"的值应为重复次数的n-1倍。在此示例中，有2个dulpics，因此rowcount为1。如果获得多个重复的行，则必须为每个唯一的主键执行此操作。

如果重复很多，则将每个密钥复制一次到另一个表：

1
2
3
4
5

SELECT col1, col2, col3=COUNT(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING COUNT(*) > 1

然后复制密钥，但消除重复项。

1
2
3
4
5

SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

现在，您的键中具有唯一键。检查是否没有任何结果：

1
2
3

SELECT col1, col2, COUNT(*)
FROM holddups
GROUP BY col1, col2

从原始表中删除重复项：

1
2
3
4

DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

插入原始行：

1	INSERT t1 SELECT * FROM holddups

顺便说一句，为了完整性：在Oracle中，您可以使用一个隐藏字段(行)：

1
2
3
4
5

DELETE FROM our_table
WHERE rowid NOT IN
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

请参阅：Microsoft知识站点

添加一个标识列作为代理主键，并使用它标识要删除的三行中的两行。

我会考虑在之后保留标识列，或者如果这是某种链接表，请在其他列上创建复合主键。

这是使用通用表表达式CTE的一种方法。它没有循环，没有新列或任何东西，并且不会引起任何不需要的触发器(由于deletes + inserts)的触发。

受本文启发。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE #temp (i INT)

INSERT INTO #temp VALUES (1)
INSERT INTO #temp VALUES (1)
INSERT INTO #temp VALUES (2)
INSERT INTO #temp VALUES (3)
INSERT INTO #temp VALUES (3)
INSERT INTO #temp VALUES (4)

SELECT * FROM #temp

;
WITH [#temp+rowid] AS
(SELECT ROW_NUMBER() OVER (ORDER BY i ASC) AS ROWID, * FROM #temp)
DELETE FROM [#temp+rowid] WHERE rowid IN
(SELECT MIN(rowid) FROM [#temp+rowid] GROUP BY i HAVING COUNT(*) > 1)

SELECT * FROM #temp

DROP TABLE #temp

这是我问这个问题时使用的方法-

1
2
3
4
5
6
7
8
9
10

DELETE MyTable
FROM MyTable
LEFT OUTER JOIN (
SELECT MIN(RowId) AS RowId, Col1, Col2, Col3
FROM MyTable
GROUP BY Col1, Col2, Col3
) AS KeepRows ON
MyTable.RowId = KeepRows.RowId
WHERE
KeepRows.RowId IS NULL

这是一个艰难的情况。在不知道您的特定情况(表大小等)的情况下，我认为您最好的选择是添加一个Identity列，填充它，然后根据它删除。您可以稍后删除该列，但我建议您保留该列，因为它确实存在于表中

那这个解决方案呢：

首先，您执行以下查询：

1	SELECT 'set rowcount ' + CONVERT(VARCHAR,COUNT()-1) + ' delete from MyTable where field=''' + FIELD +'''' + ' set rowcount 0' FROM mytable GROUP BY FIELD HAVING COUNT()>1

然后，您只需要执行返回的结果集

1
2
3
4

SET rowcount 3 DELETE FROM Mytable WHERE FIELD='foo' SET rowcount 0
....
....
SET rowcount 5 DELETE FROM Mytable WHERE FIELD='bar' SET rowcount 0

当您只有一列时，我已经处理了这种情况，但是很容易将相同的方法应用于一列以上。如果您要我发布代码，请告诉我。

怎么样：

1
2
3
4
5
6
7

SELECT DISTINCT * INTO #t FROM duplicates_tbl

TRUNCATE duplicates_tbl

INSERT duplicates_tbl SELECT * FROM #t

DROP TABLE #t

这是测试数据的另一种方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE TABLE #table1 (colWithDupes1 INT, colWithDupes2 INT)
INSERT INTO #table1
(colWithDupes1, colWithDupes2)
SELECT 1, 2 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 4 UNION ALL
SELECT 3, 4 UNION ALL
SELECT 3, 4 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 4, 2

SELECT * FROM #table1

SET rowcount 1
SELECT 1

while @@rowcount > 0
DELETE #table1 WHERE 1 < (SELECT COUNT(*) FROM #table1 a2
WHERE #table1.colWithDupes1 = a2.colWithDupes1
AND #table1.colWithDupes2 = a2.colWithDupes2
)

SET rowcount 0

SELECT * FROM #table1

Manrico Corazzi-我专门研究Oracle，而不是MS SQL，因此，您必须告诉我是否有可能提高性能：-

与第一步相同-将不同的值插入TABLE1中的TABLE2中。

删除TABLE1。 (丢弃应该比我认为的快，就像截断要比删除快一样)。

将TABLE2重命名为TABLE1(节省时间，因为您要重命名对象而不是将数据从一个表复制到另一个表)。

您可以在表中添加主键标识字段吗？

清理完当前混乱之后，您可以添加一个主键，其中包含表中的所有字段。这样可以避免您再次陷入困境。
当然，此解决方案很可能会破坏现有代码。那也必须处理。

我不确定这是否适用于DELETE语句，但这是查找重复行的一种方式：

1
2
3