仅使用一个查询删除重复记录 [英] Deletion of duplicate records using one query only

问题描述

我使用的是 SQL Server 2005.

I am using SQL server 2005.

我有一张这样的桌子 -

I have a table like this -

ID    Name
1      a
1      a
1      a
2      b
2      b
3      c
4      d
4      d

在这里，我想删除所有重复的条目并只保留一个实例 -

In this, I want to delete all duplicate entries and retain only one instance as -

ID     Name
1       a
2       b
3       c
4       d

通过向该表添加另一个标识列并在其中包含唯一编号，然后删除重复记录，我可以轻松完成此操作.但是我想知道我是否可以删除重复的记录而无需向此表中添加该附加列.

I can do this easily by adding another identity column to this table and having unique numbers in it and then deleting the duplicate records. However I want to know if I can delete the duplicate records without adding that additional column to this table.

此外，如果这可以仅使用一个查询语句来完成.即不使用存储过程或临时表.

Additionally if this can be done using only one query statement. i.e. Without using Stored procedures or temp tables.

推荐答案

使用 CTE 中的 ROW_NUMBER 允许您删除重复值，同时保留唯一行.

Using a ROW_NUMBER in a CTE allows you to delete duplicate values while retaining unique rows.

WITH q AS (
  SELECT RN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID )
         , ID
         , Name
  FROM   ATable
)
DELETE FROM q WHERE RN > 1

这篇关于仅使用一个查询删除重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！