데이터베이스 를 관리하다보면 중복데이터가 만들어 지는경우가 상당히 많다. 정상적으로 들어가 있지만 중복제거된 데이터를 만들어야 하거나 외부데이터를 임포트 하는 과정에서 중복 데이터가 입력되는 경우도 자주 있다. 이런 경우 중복 데이터 제거 방법에 대해 알아보자

필요 칼럼들 모두에 대한 중복제거 데이터가 필요한 경우

 
Select distinct col1, col2, col3 from test;
 

distinct 구문으로 간단하게 필요한 데이터를 뽑아올 수 있다.

특정 칼럼에 대해서만 중복 제거가 필요할 경우 

sql server 2005 이상 버전

Col1 칼럼에 대해 중복 제거가 필요한대 col2 의 값이 가장 작은것을 남기고자 할때, 예를 들어 한로우에 state, city, zipcode 정보가 있을때 각 도시별로 가장 작은 zipcode 만 남기고 싶을때

 
SELECT state, city, zipcode

FROM (

SELECT state, city, zipcode, row_number() over(partition by state, city order by state, city, zipcode) rnum FROM test

) a

WHERE rnum=1;

 

sql server 2000 이하 버전

col1 칼럼에 대해 중복 제거가 필요한대 col2 의 길이가 가장 긴것을 남기고자 할때

 
SELECT * into test_distinct FROM test where 1=2; -- 빈 테이블 복사생성

CREATE UNIQUE INDEX unique_idx ON test_distinct (col1) WITH IGNORE_DUP_KEY; --중복제거를 위한 인덱스 생성

INSERT INTO test_distinct (col1, col2, col3)

SELECT col1, col2, col3 FROM test ORDER BY col1, len(col2) DESC;
 

위와같이 실행할경우 각  col1 별로 col2 가 가장긴 레코드만 남고 나머지는 중복제거 된다.

현재 테이블을 교체할 수 없고 테이블 내에서 중복 데이터의 삭제가 필요할 경우

sql server 2005 이상 버전

 
WITH test_cte
As
(
Select col1, col2, row_number() over (partition by col1 order by col2) rnum
)

Delete test_cte where rnum>1;
 

sql server 2000 이하 버전

col1 별로 최소값을 갖는 col2 행만 남기고 나머지 행들을 삭제하는 방법 1. (삭제전 select 쿼리로 삭제될 데이터를 확인할것)

 
DELETE test

FROM test a 

	INNER JOIN 

(SELECT col1, MIN(col2) min_col2 FROM test GROUP BY col1) b ON a.col1=b.col1 and a.col2<>b.min_col2;
 

col1 별로 최소값을 갖는 col2 행만 남기고 나머지 행들을 삭제하는 방법 2. (삭제전 select 쿼리로 삭제될 데이터를 확인할것)

 
DELETE a 

FROM test a, test b WHERE a.col2 > b.col2 AND b.col1=a.col1
 

위와 반대로 col1 별로 최대값을 갖는 col2 행만 남기고 나머지 행들을 삭제하는 방법. (삭제전 select 쿼리로 삭제될 데이터를 확인할것)

 
DELETE a 

FROM test a, test b WHERE a.col2 < b.col2 AND b.col1=a.col1