删除SQL Server中的重复记录?

Consider a column named EmployeeName table Employee. The goal is to delete repeated records, based on the EmployeeName field.

考虑一个名为EmployeeName表Employee的列。目标是基于EmployeeName字段删除重复记录。

EmployeeName
------------
Anand
Anand
Anil
Dipak
Anil
Dipak
Dipak
Anil

Using one query, I want to delete the records which are repeated.

使用一个查询，我想删除重复的记录。

How can this be done with TSQL in SQL Server?

如何在SQL Server中使用TSQL来实现这一点?

9 个解决方案

#1

You can do this with window functions. It will order the dupes by empId, and delete all but the first one.

你可以用窗口函数来实现。它会用pid命令dupes，并删除除第一个。

delete x from (
  select *, rn=row_number() over (partition by EmployeeName order by empId)
  from Employee 
) x
where rn > 1;

Run it as a select to see what would be deleted:

运行它作为一个选择，看看什么将被删除:

select *
from (
  select *, rn=row_number() over (partition by EmployeeName order by empId)
  from Employee 
) x
where rn > 1;

#2

Assuming that your Employee table also has a unique column (ID in the example below), the following will work:

假设您的Employee表也有一个惟一的列(在下面的示例中是ID)，下面的代码将会工作:

delete from Employee 
where ID not in
(
    select min(ID)
    from Employee 
    group by EmployeeName 
);

This will leave the version with the lowest ID in the table.

这将使版本具有表中最低的ID。

Edit
Re McGyver's comment - as of SQL 2012

编辑remcgyver的评论——从SQL 2012年开始

MIN can be used with numeric, char, varchar, uniqueidentifier, or datetime columns, but not with bit columns

MIN可以与数字、char、varchar、惟一标识符或datetime列一起使用，但不能使用位列。

For 2008 R2 and earlier,

2008 R2及更早的时候，

MIN can be used with numeric, char, varchar, or datetime columns, but not with bit columns (and it also doesn't work with GUID's)

MIN可以用于数字、char、varchar或datetime列，但不能用于位列(它也不能用于GUID)

For 2008R2 you'll need to cast the GUID to a type supported by MIN, e.g.

对于2008R2，您需要将GUID转换为MIN支持的类型，例如。

delete from GuidEmployees
where CAST(ID AS binary(16)) not in
(
    select min(CAST(ID AS binary(16)))
    from GuidEmployees
    group by EmployeeName 
);

SqlFiddle for various types in Sql 2008

Sql 2008中各种类型的SqlFiddle

SqlFiddle for various types in Sql 2012

Sql 2012中各种类型的SqlFiddle

#3

You could try something like the following:

你可以试试以下方法:

delete T1
from MyTable T1, MyTable T2
where T1.dupField = T2.dupField
and T1.uniqueField > T2.uniqueField  

(this assumes that you have an integer based unique field)

(假设您有一个基于整数的惟一字段)

Personally though I'd say you were better off trying to correct the fact that duplicate entries are being added to the database before it occurs rather than as a post fix-it operation.

就我个人而言，我认为您最好尝试纠正一个事实，即在数据库发生之前，重复的条目正在被添加到数据库中，而不是作为post - fix-it操作。

#4

DELETE
FROM MyTable
WHERE ID NOT IN (
     SELECT MAX(ID)
     FROM MyTable
     GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

---

WITH TempUsers (FirstName, LastName, duplicateRecordCount)
AS
(
    SELECT FirstName, LastName,
    ROW_NUMBER() OVER (PARTITIONBY FirstName, LastName ORDERBY FirstName) AS duplicateRecordCount
    FROM dbo.Users
)
DELETE
FROM TempUsers
WHERE duplicateRecordCount > 1

#5

WITH CTE AS
(
   SELECT EmployeeName, 
          ROW_NUMBER() OVER(PARTITION BY EmployeeName ORDER BY EmployeeName) AS R
   FROM employee_table
)
DELETE CTE WHERE R > 1;

The magic of common table expressions.

通用表表达式的魔力。

#6

Try

试一试

DELETE
FROM employee
WHERE rowid NOT IN (SELECT MAX(rowid) FROM employee
GROUP BY EmployeeName);

#7

If you're looking for a way to remove duplicates, yet you have a foreign key pointing to the table with duplicates, you could take the following approach using a slow yet effective cursor.

如果您正在寻找一种删除重复的方法，但是您有一个外键指向具有重复的表，您可以使用一个缓慢但有效的游标来使用以下方法。

It will relocate the duplicate keys on the foreign key table.

它将重新定位外键表上的重复键。

create table #properOlvChangeCodes(
    id int not null,
    name nvarchar(max) not null
)

DECLARE @name VARCHAR(MAX);
DECLARE @id INT;
DECLARE @newid INT;
DECLARE @oldid INT;

DECLARE OLVTRCCursor CURSOR FOR SELECT id, name FROM Sales_OrderLineVersionChangeReasonCode; 
OPEN OLVTRCCursor;
FETCH NEXT FROM OLVTRCCursor INTO @id, @name;
WHILE @@FETCH_STATUS = 0  
BEGIN  
        -- determine if it should be replaced (is already in temptable with name)
        if(exists(select * from #properOlvChangeCodes where Name=@name)) begin
            -- if it is, finds its id
            Select  top 1 @newid = id
            from    Sales_OrderLineVersionChangeReasonCode
            where   Name = @name

            -- replace terminationreasoncodeid in olv for the new terminationreasoncodeid
            update Sales_OrderLineVersion set ChangeReasonCodeId = @newid where ChangeReasonCodeId = @id

            -- delete the record from the terminationreasoncode
            delete from Sales_OrderLineVersionChangeReasonCode where Id = @id
        end else begin
            -- insert into temp table if new
            insert into #properOlvChangeCodes(Id, name)
            values(@id, @name)
        end

        FETCH NEXT FROM OLVTRCCursor INTO @id, @name;
END;
CLOSE OLVTRCCursor;
DEALLOCATE OLVTRCCursor;

drop table #properOlvChangeCodes

#8

Here's a nice way of deduplicating records in a table that has an identity column based on a desired primary key that you can define at runtime. Before I start I'll populate a sample data set to work with using the following code:

这里有一种很好的方法，可以在一个具有标识列的表中，根据需要的主键，在运行时定义一个标识列。在开始之前，我将填充一个示例数据集，以使用以下代码进行工作:

if exists (select 1 from sys.all_objects where type='u' and name='_original')
drop table _original

declare @startyear int = 2017
declare @endyear int = 2018
declare @iterator int = 1
declare @income money = cast((SELECT round(RAND()*(5000-4990)+4990 , 2)) as money)
declare @salesrepid int = cast(floor(rand()*(9100-9000)+9000) as varchar(4))
create table #original (rowid int identity, monthyear varchar(max), salesrepid int, sale money)
while @iterator<=50000 begin
insert #original 
select (Select cast(floor(rand()*(@endyear-@startyear)+@startyear) as varchar(4))+'-'+ cast(floor(rand()*(13-1)+1) as varchar(2)) ),  @salesrepid , @income
set  @salesrepid  = cast(floor(rand()*(9100-9000)+9000) as varchar(4))
set @income = cast((SELECT round(RAND()*(5000-4990)+4990 , 2)) as money)
set @iterator=@iterator+1
end  
update #original
set monthyear=replace(monthyear, '-', '-0') where  len(monthyear)=6

select * into _original from #original

Next I'll create a Type called ColumnNames:

接下来我将创建一个名为ColumnNames的类型:

create type ColumnNames AS table   
(Columnnames varchar(max))

Finally I will create a stored proc with the following 3 caveats: 1. The proc will take a required parameter @tablename that defines the name of the table you are deleting from in your database. 2. The proc has an optional parameter @columns that you can use to define the fields that make up the desired primary key that you are deleting against. If this field is left blank, it is assumed that all the fields besides the identity column make up the desired primary key. 3. When duplicate records are deleted, the record with the lowest value in it's identity column will be maintained.

最后，我将创建一个存储的proc，下面3个警告:1。proc将使用一个必需的参数@tablename，该参数定义要从数据库中删除的表的名称。2。proc有一个可选的参数@columns，您可以使用它来定义组成所需的主键的字段。如果该字段为空，则假定标识列之外的所有字段组成所需的主键。3所示。当删除重复的记录时，将维护标识列中值最低的记录。

Here is my delete_dupes stored proc:

这是我的delete_dupes存储的proc:

 create proc delete_dupes (@tablename varchar(max), @columns columnnames readonly) 
 as
 begin

declare @table table (iterator int, name varchar(max), is_identity int)
declare @tablepartition table (idx int identity, type varchar(max), value varchar(max))
declare @partitionby varchar(max)  
declare @iterator int= 1 


if exists (select 1 from @columns)  begin
declare @columns1 table (iterator int, columnnames varchar(max))
insert @columns1
select 1, columnnames from @columns
set @partitionby = (select distinct 
                substring((Select ', '+t1.columnnames 
                From @columns1 t1
                Where T1.iterator = T2.iterator
                ORDER BY T1.iterator
                For XML PATH ('')),2, 1000)  partition
From @columns1 T2 )

end

insert @table 
select 1, a.name, is_identity from sys.all_columns a join sys.all_objects b on a.object_id=b.object_id
where b.name = @tablename  

declare @identity varchar(max)= (select name from @table where is_identity=1)

while @iterator>=0 begin 
insert @tablepartition
Select          distinct case when @iterator=1 then 'order by' else 'over (partition by' end , 
                substring((Select ', '+t1.name 
                From @table t1
                Where T1.iterator = T2.iterator and is_identity=@iterator
                ORDER BY T1.iterator
                For XML PATH ('')),2, 5000)  partition
From @table T2
set @iterator=@iterator-1
end 

declare @originalpartition varchar(max)

if @partitionby is null begin
select @originalpartition  = replace(b.value+','+a.type+a.value ,'over (partition by','')  from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1
select @partitionby = a.type+a.value+' '+b.type+a.value+','+b.value+') rownum' from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1
 end
 else
 begin
 select @originalpartition=b.value +','+ @partitionby from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1
 set @partitionby = (select 'OVER (partition by'+ @partitionby  + ' ORDER BY'+ @partitionby + ','+b.value +') rownum'
 from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1)
 end


exec('select row_number() ' + @partitionby +', '+@originalpartition+' into ##temp from '+ @tablename+'')


exec(
'delete a from _original a 
left join ##temp b on a.'+@identity+'=b.'+@identity+' and rownum=1  
where b.rownum is null')

drop table ##temp

end

Once this is complied, you can delete all your duplicate records by running the proc. To delete dupes without defining a desired primary key use this call:

一旦被执行，您可以通过运行proc来删除所有重复的记录。

exec delete_dupes '_original'

To delete dupes based on a defined desired primary key use this call:

要删除基于已定义的所需主键的dupes，请使用以下调用:

declare @table1 as columnnames
insert @table1
values ('salesrepid'),('sale')
exec delete_dupes '_original' , @table1

#9

Please see the below way of deletion too.

请见下面的删除方式。

Declare @Employee table (EmployeeName varchar(10))

Insert into @Employee values 
('Anand'),('Anand'),('Anil'),('Dipak'),
('Anil'),('Dipak'),('Dipak'),('Anil')

Select * from @Employee

Created a sample table named @Employee and loaded it with given data.

创建一个名为@Employee的示例表，并将它装载到给定的数据中。

Delete  aliasName from (
Select  *,
        ROW_NUMBER() over (Partition by EmployeeName order by EmployeeName) as rowNumber
From    @Employee) aliasName 
Where   rowNumber > 1

Select * from @Employee

Result:

结果:

I know, this is asked six years ago, posting just incase it is helpful for anyone.

我知道，这是六年前的问题，发布只是为了防止它对任何人都有帮助。