SQL Server에서 중복 레코드를 삭제 하시겠습니까?

SQL Server에서 중복 레코드를 삭제 하시겠습니까?

---

EmployeeNametable 이라는 열을 고려하십시오 Employee. 목표는 EmployeeName필드를 기반으로 반복되는 레코드를 삭제하는 것 입니다.

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

하나의 쿼리를 사용하여 반복되는 레코드를 삭제하고 싶습니다.

SQL Server에서 TSQL로 어떻게이 작업을 수행 할 수 있습니까?

---

창 기능으로이를 수행 할 수 있습니다. 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;

---

Employee 테이블에도 고유 한 열이 있다고 가정하면 ( ID아래 예에서) 다음이 작동합니다.

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

그러면 테이블에서 ID가 가장 낮은 버전이 남습니다.

Re McGyver의 주석 편집 -SQL 2012 기준

MIN numeric, 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 
);

Sql 2008의 다양한 유형에 대한 SqlFiddle

Sql 2012의 다양한 유형에 대한 SqlFiddle

---

다음과 같은 것을 시도 할 수 있습니다.

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

(이것은 정수 기반 고유 필드가 있다고 가정합니다)

개인적으로는 수정 후 작업보다는 중복 항목이 데이터베이스에 추가되기 전에 데이터베이스에 추가된다는 사실을 수정하는 것이 더 낫다고 말하고 싶습니다.

---

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

---

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

일반적인 테이블 표현식의 마법.

---

시험

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

---

중복을 제거하는 방법을 찾고 있지만 중복이있는 테이블을 가리키는 외래 키가있는 경우 느리지 만 효과적인 커서를 사용하여 다음 방법을 사용할 수 있습니다.

외래 키 테이블에서 중복 키를 재배치합니다.

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

---

다음은 런타임에 정의 할 수있는 원하는 기본 키를 기반으로 ID 열이있는 테이블에서 레코드를 중복 제거하는 좋은 방법입니다. 시작하기 전에 다음 코드를 사용하여 작업 할 샘플 데이터 세트를 채울 것입니다.

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

다음으로 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.

Here is my delete_dupes stored 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:

exec delete_dupes '_original'

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

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

---

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.

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.

참고URL : https://stackoverflow.com/questions/3317433/delete-duplicate-records-in-sql-server