Удалить дубликаты записей в SQL Server?

Ответ 1

Вы можете сделать это с помощью оконных функций. Он будет заказывать обманы empId и удалять все, кроме первого.

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

Запустите его как выбор, чтобы увидеть, что будет удалено:

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

Ответ 2

Предполагая, что таблица Employee также имеет уникальный столбец (ID в приведенном ниже примере), будет работать следующее:

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

Это оставит версию с самым низким идентификатором в таблице.

Edit
Комментарий Re McGyver - от SQL 2012

MIN может использоваться с числовыми столбцами char, varchar, uniqueidentifier или datetime, но не с столбцами бит

Для 2008 R2 и ранее

MIN может использоваться с числовыми столбцами char, varchar или datetime, но не с битовыми столбцами (а также не работает с GUID)

В 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 для различных типов в Sql 2008

SqlFiddle для различных типов в Sql 2012

Ответ 3

Вы можете попробовать что-то вроде следующего:

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

(это предполагает, что у вас есть целочисленное уникальное поле)

Лично, хотя я бы сказал, что вам лучше исправить тот факт, что дублирующие записи добавляются в базу данных до ее появления, а не как операция 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;

Магия общих табличных выражений.

Ответ 6

Try

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

Ответ 7

Если вы ищете способ удаления дубликатов, но у вас есть внешний ключ, указывающий на таблицу с дубликатами, вы можете использовать следующий подход, используя медленный, но эффективный курсор.

Он переместит дубликаты ключей в таблицу внешнего ключа.

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 [email protected])) 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

Вот хороший способ дедупликации записей в таблице, которая имеет столбец идентификатора на основе желаемого первичного ключа, который вы можете определить во время выполнения. Прежде чем начать, я заполню образец набора данных, чтобы работать с использованием следующего кода:

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()*(@[email protected])[email protected]) 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 @[email protected]+1
end  
update #original
set monthyear=replace(monthyear, '-', '-0') where  len(monthyear)=6

select * into _original from #original

Далее я создам тип с именем ColumnNames:

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

Наконец, я создам сохраненную процедуру со следующими тремя оговорками: 1. proc примет требуемый параметр @tablename, который определяет имя таблицы, которую вы удаляете из своей базы данных. 2. В proc есть необязательный параметр @columns, который вы можете использовать для определения полей, составляющих нужный первичный ключ, который вы удаляете. Если это поле остается пустым, предполагается, что все поля, кроме столбца идентификации, составляют нужный первичный ключ. 3. Когда дубликаты записей будут удалены, запись с наименьшим значением в столбце идентификации будет сохранена.

Вот мой файл 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 [email protected]
                ORDER BY T1.iterator
                For XML PATH ('')),2, 5000)  partition
From @table T2
set @[email protected]
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 +', '[email protected]+' into ##temp from '+ @tablename+'')


exec(
'delete a from _original a 
left join ##temp b on a.'[email protected]+'=b.'[email protected]+' and rownum=1  
where b.rownum is null')

drop table ##temp

end

Как только это будет выполнено, вы можете удалить все повторяющиеся записи, запустив proc. Для удаления дубликатов без указания нужного первичного ключа используйте этот вызов:

exec delete_dupes '_original'

Для удаления дубликатов на основе определенного целевого первичного ключа используйте этот вызов:

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

Ответ 9

delete from person 
where ID not in
(
        select t.id from 
        (select min(ID) as id from person 
         group by email 
        ) as t
);

Ответ 10

См. также способ удаления.

Declare @Employee table (EmployeeName varchar(10))

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

Select * from @Employee

Создал примерную таблицу с именем @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

Результат:

Я знаю, об этом спрашивают шесть лет назад, выставляя сообщение просто, что это полезно для всех.