代码之家 › 专栏 › 技术社区 › Mason Wheeler

如何删除表中的重复记录?

sql-server-2008 sql

2

Mason Wheeler · 技术社区 · 15 年前

ID UNIQUEIDENTIFIER
TYPE_INT SMALLINT
SYSTEM_VALUE SMALLINT
NAME VARCHAR
MAPPED_VALUE VARCHAR

应该有几十排。它大约有200000个,其中大部分是重复的,其中类型\ INT、系统\值、名称和映射\值都是相同的,而ID不是。

现在,我可以编写一个脚本来清理这个问题,在内存中创建一个临时表,使用 INSERT .. SELECT DISTINCT 为了抓住所有独特的价值, TRUNCATE DELETE 查询中有特殊内容 WHERE

3 回复 | 直到 15 年前

1

4

Martin Smith 15 年前

你没有给出你的表名,但我认为这样做应该有效。只留下恰好具有最低ID的记录。您可能希望首先使用回滚进行测试!

BEGIN TRAN
DELETE <table_name>
FROM <table_name> T1
WHERE EXISTS(
SELECT * FROM <table_name> T2 
WHERE     
T1.TYPE_INT = T2.TYPE_INT  AND
T1.SYSTEM_VALUE = T2.SYSTEM_VALUE  AND
T1.NAME = T2.NAME  AND
T1.MAPPED_VALUE = T2.MAPPED_VALUE  AND
T2.ID > T1.ID
)

SELECT * FROM <table_name>

ROLLBACK

2

3

KM. 15 年前

下面是一篇关于这方面的文章: Deleting duplicates ,它基本上使用这种模式:

WITH    q AS
        (
        SELECT  d.*,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY value) AS rn
        FROM    t_duplicate d
        )
DELETE
FROM    q
WHERE   rn > 1

SELECT  *
FROM    t_duplicate

3

2

Glennular 15 年前

WITH Duplicates(ID , TYPE_INT, SYSTEM_VALUE, NAME, MAPPED_VALUE )
AS
(
SELECT  Min(Id)  ID  TYPE_INT, SYSTEM_VALUE, NAME, MAPPED_VALUE 
FROM T1
GROUP BY TYPE_INT, SYSTEM_VALUE, NAME, MAPPED_VALUE
HAVING Count(Id) > 1
)
DELETE FROM T1
WHERE ID IN (
SELECT T1.Id
FROM T1
INNER JOIN Duplicates
ON T1.TYPE_INT = Duplicates.TYPE_INT
AND T1.SYSTEM_VALUE = Duplicates.SYSTEM_VALUE
AND T1.NAME = Duplicates.NAME
AND T1.MAPPED_VALUE = Duplicates.MAPPED_VALUE
AND T1.Id <> Duplicates.ID
)