중복 행을 제거하려면 어떻게 해야 합니까?

질문자 :Seibar

SQL Server 테이블(즉, 300,000개 이상의 행)에서 중복 행을 제거하는 가장 좋은 방법은 무엇입니까?

RowID ID 필드가 있기 때문에 완벽하게 중복되지 않습니다.

마이테이블

 RowID int not null identity(1,1) primary key, Col1 varchar(20) not null, Col2 varchar(2048) not null, Col3 tinyint not null

더 널 (null), 당신이 없다고 가정 GROUP BY 고유 열 및 SELECT MIN (or MAX) 유지하는 행으로의 RowId를. 그런 다음 행 ID가 없는 모든 것을 삭제합니다.

 DELETE FROM MyTable LEFT OUTER JOIN ( SELECT MIN(RowId) as RowId, Col1, Col2, Col3 FROM MyTable GROUP BY Col1, Col2, Col3 ) as KeepRows ON MyTable.RowId = KeepRows.RowId WHERE KeepRows.RowId IS NULL

정수 대신 GUID가 있는 경우 다음을 대체할 수 있습니다.

 MIN(RowId)

~와 함께

 CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

Mark Brackett

이 작업을 수행할 수 있는 또 다른 방법은

 ; --Ensure that any immediately preceding statement is terminated with a semicolon above WITH cte AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 ORDER BY ( SELECT 0)) RN FROM #MyTable) DELETE FROM cte WHERE RN > 1;

ORDER BY (SELECT 0) 를 사용하고 있습니다. 동점일 경우 어떤 행을 보존할지가 임의적이기 때문입니다.

RowID 순서의 최신 항목을 유지하려면 ORDER BY RowID DESC 사용할 수 있습니다.

실행 계획

이에 대한 실행 계획은 자체 조인이 필요하지 않기 때문에 허용된 답변보다 더 간단하고 효율적입니다.

그러나 항상 그런 것은 아닙니다. GROUP BY 솔루션이 선호되는 한 곳은 스트림 집계보다 해시 집계가 선택되는 상황입니다.

ROW_NUMBER 솔루션은 항상 거의 동일한 계획을 제공하는 반면 GROUP BY 전략은 더 유연합니다.

해시 집계 접근 방식을 선호할 수 있는 요소는 다음과 같습니다.

• 분할 열에 유용한 인덱스가 없습니다.
• 각 그룹에 상대적으로 더 많은 중복이 있는 상대적으로 적은 수의 그룹

이 두 번째 경우의 극단적인 버전(각각에 많은 중복이 있는 그룹이 거의 없는 경우)에서는 단순히 행을 삽입하여 새 테이블에 유지한 다음 TRUNCATE - 원본을 삭제하고 삭제와 비교하여 로깅을 최소화하기 위해 다시 복사하는 것을 고려할 수도 있습니다. 행의 비율이 매우 높습니다.

Martin Smith

Microsoft 지원 사이트에 중복 제거 에 대한 좋은 기사가 있습니다. 매우 보수적입니다. 모든 작업을 별도의 단계로 수행해야 하지만 큰 테이블에 대해 잘 작동해야 합니다.

과거에는 자체 조인을 사용하여 이 작업을 수행한 적이 있지만 HAVING 절을 사용하면 더 멋질 수 있습니다.

 DELETE dupes FROM MyTable dupes, MyTable fullTable WHERE dupes.dupField = fullTable.dupField AND dupes.secondDupField = fullTable.secondDupField AND dupes.uniqueField > fullTable.uniqueField

Jon Galloway

다음 쿼리는 중복 행을 삭제하는 데 유용합니다. 이 예제의 테이블은 ID 열로 ID를 가지며 중복 데이터가 있는 Column1 은 Column1 , Column2 및 Column3 입니다.

 DELETE FROM TableName WHERE ID NOT IN (SELECT MAX(ID) FROM TableName GROUP BY Column1, Column2, Column3 /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially nullable. Because of semantics of NOT IN (NULL) including the clause below can simplify the plan*/ HAVING MAX(ID) IS NOT NULL)

GROUP BY , HAVING , ORDER BY 사용을 보여주고 중복 열과 해당 개수가 포함된 결과를 반환합니다.

 SELECT YourColumnName, COUNT(*) TotalCount FROM YourTableName GROUP BY YourColumnName HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

gngolakia

delete t1 from table t1, table t2 where t1.columnA = t2.columnA and t1.rowid>t2.rowid

포스트그레스:

 delete from table t1 using table t2 where t1.columnA = t2.columnA and t1.rowid > t2.rowid

SoftwareGeek

DELETE LU FROM (SELECT *, Row_number() OVER ( partition BY col1, col1, col3 ORDER BY rowid DESC) [Row] FROM mytable) LU WHERE [row] > 1

Jithin Shaji

첫 번째 행을 제외한 중복 행이 삭제됩니다.

 DELETE FROM Mytable WHERE RowID NOT IN ( SELECT MIN(RowID) FROM Mytable GROUP BY Col1, Col2, Col3 )

참조 ( http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server )

Syed Mohamed

SQL Server 테이블에서 중복 행을 삭제하려면 CTE를 선호합니다.

이 기사를 따르는 것이 좋습니다 :: http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/

원본을 유지하여

 WITH CTE AS ( SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN FROM MyTable ) DELETE FROM CTE WHERE RN<>1

원본을 유지하지 않고

 WITH CTE AS (SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3) FROM MyTable) DELETE CTE WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)

Shamseer K

중복 행을 가져오려면:

 SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) > 1

중복 행을 삭제하려면:

 DELETE users WHERE rowid NOT IN (SELECT MIN(rowid) FROM users GROUP BY name, email);

Shaini Sinha

정확한 중복 행을 삭제하는 빠르고 더티(작은 테이블의 경우):

 select distinct * into t2 from t1; delete from t1; insert into t1 select * from t2; drop table t2;

JuanJo

나는 더 읽기 쉽고 SELECT 문으로 변환하여 실행하기 전에 삭제할 항목을 확인하는 것이 매우 쉽기 때문에 내부 조인보다 subquery\have count(*) > 1 솔루션을 선호합니다.

 --DELETE FROM table1 --WHERE id IN ( SELECT MIN(id) FROM table1 GROUP BY col1, col2, col3 -- could add a WHERE clause here to further filter HAVING count(*) > 1 --)

James Errico

SELECT DISTINCT * INTO tempdb.dbo.tmpTable FROM myTable TRUNCATE TABLE myTable INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable DROP TABLE tempdb.dbo.tmpTable

heta77

특별한 상황에서 작동하기 때문에 내 솔루션을 공유할 것이라고 생각했습니다. 제 경우에는 중복 값이 있는 테이블에 외래 키가 없었습니다(값이 다른 db에서 중복되었기 때문에).

 begin transaction -- create temp table with identical structure as source table Select * Into #temp From tableName Where 1 = 2 -- insert distinct values into temp insert into #temp select distinct * from tableName -- delete from source delete from tableName -- insert into source from temp insert into tableName select * from #temp rollback transaction -- if this works, change rollback to commit and execute again to keep you changes!!

추신: 이와 같은 작업을 할 때 저는 항상 트랜잭션을 사용합니다. 이렇게 하면 모든 것이 전체적으로 실행될 뿐만 아니라 어떤 위험도 감수하지 않고 테스트할 수 있습니다. 하지만 물론 백업을 해야 확실하게 할 수 있습니다...

Ruben Verschueren

이 쿼리는 저에게 매우 좋은 성능을 보여주었습니다.

 DELETE tbl FROM MyTable tbl WHERE EXISTS ( SELECT * FROM MyTable tbl2 WHERE tbl2.SameValue = tbl.SameValue AND tbl.IdUniqueValue < tbl2.IdUniqueValue )

2M 테이블에서 30초도 안 되는 시간에 1M 행을 삭제했습니다(50% 중복).

Draško

CTE 사용. 아이디어는 중복 레코드를 형성하는 하나 이상의 열을 결합한 다음 원하는 것을 제거하는 것입니다.

 ;with cte as ( select min(PrimaryKey) as PrimaryKey UniqueColumn1, UniqueColumn2 from dbo.DuplicatesTable group by UniqueColumn1, UniqueColumn1 having count(*) > 1 ) delete d from dbo.DuplicatesTable d inner join cte on d.PrimaryKey > cte.PrimaryKey and d.UniqueColumn1 = cte.UniqueColumn1 and d.UniqueColumn2 = cte.UniqueColumn2;

Ostati

또 다른 쉬운 솔루션은 여기에 붙여넣은 링크에서 찾을 수 있습니다. 이것은 이해하기 쉽고 대부분의 유사한 문제에 효과적인 것 같습니다. SQL Server용이지만 사용된 개념은 허용되는 것 이상입니다.

링크된 페이지의 관련 부분은 다음과 같습니다.

다음 데이터를 고려하십시오.

 EMPLOYEE_ID ATTENDANCE_DATE A001 2011-01-01 A001 2011-01-01 A002 2011-01-01 A002 2011-01-01 A002 2011-01-01 A003 2011-01-01

그렇다면 이러한 중복 데이터를 어떻게 삭제할 수 있습니까?

먼저 다음 코드를 사용하여 해당 테이블에 ID 열을 삽입합니다.

 ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)

다음 코드를 사용하여 해결하십시오.

 DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _ FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

Nitish Pareek

이것을 사용

 WITH tblTemp as ( SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name) As RowNumber,* FROM <table_name> ) DELETE FROM tblTemp where RowNumber >1

Haris N I

여기 중복 제거 에 대한 또 다른 좋은 기사가 있습니다.

그것이 어려운 이유에 대해 설명합니다. " SQL은 관계형 대수학을 기반으로 하며, 중복은 집합에서 허용되지 않기 때문에 관계형 대수학에서는 중복이 발생할 수 없습니다. "

임시 테이블 솔루션과 두 개의 mysql 예제.

앞으로는 데이터베이스 수준에서 또는 애플리케이션 관점에서 이를 방지할 것입니다. 나는 당신의 데이터베이스가 참조 무결성을 유지해야 하기 때문에 데이터베이스 수준을 제안할 것입니다. 개발자는 문제를 일으킬 뿐입니다 ;)

Craig

중복되지 않은 행을 보존해야 하는 테이블이 있었습니다. 속도나 효율성은 잘 모르겠습니다.

 DELETE FROM myTable WHERE RowID IN ( SELECT MIN(RowID) AS IDNo FROM myTable GROUP BY Col1, Col2, Col3 HAVING COUNT(*) = 2 )

codegoalie

그렇지. 임시 테이블을 사용합니다. "작동"하는 성능이 좋지 않은 단일 명령문을 원하면 다음을 사용할 수 있습니다.

 DELETE FROM MyTable WHERE NOT RowID IN (SELECT (SELECT TOP 1 RowID FROM MyTable mt2 WHERE mt2.Col1 = mt.Col1 AND mt2.Col2 = mt.Col2 AND mt2.Col3 = mt.Col3) FROM MyTable mt)

기본적으로 테이블의 각 행에 대해 하위 선택은 고려 중인 행과 정확히 동일한 모든 행의 최상위 RowID를 찾습니다. 따라서 중복되지 않은 "원본" 행을 나타내는 RowID 목록이 생성됩니다.

Jacob Proffitt

이것은 중복 레코드를 삭제하는 가장 쉬운 방법입니다

 DELETE FROM tblemp WHERE id IN ( SELECT MIN(id) FROM tblemp GROUP BY title HAVING COUNT(id)>1 )

Harikesh Yadav

다른 방법은 동일한 필드 와 고유 인덱스를 사용하여 새 테이블을 만드는 것입니다. 그런 다음 이전 테이블의 모든 데이터를 새 테이블로 이동합니다 . 자동으로 SQL SERVER는 중복 값을 무시합니다(중복 값이 있는 경우 수행할 작업에 대한 옵션도 있습니다: 무시, 인터럽트 또는 sth). 따라서 중복 행이 없는 동일한 테이블이 있습니다. Unique Index를 원하지 않으면 데이터 전송 후 삭제할 수 있습니다.

특히 더 큰 테이블의 경우 모든 데이터를 고유하게 인덱싱된 새 테이블로 빠르게 전송하기 위해 DTS(데이터 가져오기/내보내기용 SSIS 패키지)를 사용할 수 있습니다. 700만 행의 경우 몇 분 밖에 걸리지 않습니다.

Ismail Yavuz

아래 쿼리를 사용하여 단일 열 또는 여러 열을 기반으로 중복 레코드를 삭제할 수 있습니다. 아래 쿼리는 두 개의 열을 기준으로 삭제하고 있습니다. 테이블 이름: testing 및 열 이름 empno,empname

 DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1) or empname not in (select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno) AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)

Sudhakar NV

1. 동일한 구조로 새 빈 테이블 만들기

2. 이와 같은 쿼리를 실행

    INSERT INTO tc_category1 SELECT * FROM tc_category GROUP BY category_id, application_id HAVING count(*) > 1

3. 그런 다음이 쿼리를 실행하십시오.

    INSERT INTO tc_category1 SELECT * FROM tc_category GROUP BY category_id, application_id HAVING count(*) = 1

Kamil

나는 이 접근 방식이 도움이 될 수 있을 뿐만 아니라 모든 SQL 서버에서 작동한다고 언급할 것입니다. 꽤 자주 1-2개의 중복이 있고 ID와 중복 수를 알고 있습니다. 이 경우:

 SET ROWCOUNT 1 -- or set to number of rows to be deleted delete from myTable where RowId = DuplicatedID SET ROWCOUNT 0

Evgueny Sedov

응용 프로그램 수준에서(불행히도). 중복을 방지하는 적절한 방법은 데이터베이스 수준에서 고유 인덱스를 사용하는 것이라는 데 동의하지만 SQL Server 2005에서는 인덱스가 900바이트만 허용되며 내 varchar(2048) 필드가 이를 날려버립니다.

성능이 얼마나 좋을지는 모르겠지만 인덱스를 사용하여 직접 수행할 수 없더라도 이를 시행하는 트리거를 작성할 수 있다고 생각합니다. 다음과 같은 것:

 -- given a table stories(story_id int not null primary key, story varchar(max) not null) CREATE TRIGGER prevent_plagiarism ON stories after INSERT, UPDATE AS DECLARE @cnt AS INT SELECT @cnt = Count(*) FROM stories INNER JOIN inserted ON ( stories.story = inserted.story AND stories.story_id != inserted.story_id ) IF @cnt > 0 BEGIN RAISERROR('plagiarism detected',16,1) ROLLBACK TRANSACTION END

또한 varchar(2048)은 나에게 생소하게 들립니다. 정말 varchar(max)가 아니어야 합니까?

DrPizza

이 작업을 수행하는 또 다른 방법:--

 DELETE A FROM TABLE A, TABLE B WHERE A.COL1 = B.COL1 AND A.COL2 = B.COL2 AND A.UNIQUEFIELD > B.UNIQUEFIELD

yuvi

DELETE FROM table_name T1 WHERE rowid > ( SELECT min(rowid) FROM table_name T2 WHERE T1.column_name = T2.column_name );

Teena

CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int) INSERT INTO car(PersonId,CarId) VALUES(1,2),(1,3),(1,2),(2,4) --SELECT * FROM car ;WITH CTE as( SELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car) DELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn>1)

AnandPhadke

제거하려는 행을 미리 보고 어떤 중복 행을 유지할지 제어하고 싶습니다. http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/ 참조

 with MYCTE as ( SELECT ROW_NUMBER() OVER ( PARTITION BY DuplicateKey1 ,DuplicateKey2 -- optional ORDER BY CreatedAt -- the first row among duplicates will be kept, other rows will be removed ) RN FROM MyTable ) DELETE FROM MYCTE WHERE RN > 1

Lauri Lubi

출처 : http:www.stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows