이 문서에서는 데이터베이스 테이블에서 중복 된 행을 찾는 방법을 보여줍니다. 이것은 매우 일반적인 초보자 질문입니다. 기본 기술은 간단합니다. 또한 “duplicates in two columns”를 찾는 방법 (#mysql IRC 채널에 대한 최근 질문)과 같은 몇 가지 변형을 보여줄 것입니다.
중복 된 행을 찾는 방법
첫 번째 단계는 행을 다른 행과 정확히 복제하는 것이 무엇인지 정의하는 것입니다. 대부분의 경우 이것은 간단합니다. 일부 열에 동일한 값이 있습니다.이 문서에서는이를 작업 정의로 삼겠습니다. “중복”이라는 개념이 더 복잡한 경우 아래 쿼리를 변경해야합니다.
이 기사에서는 다음 샘플 데이터를 사용합니다.
처음 두 행은 동일합니다. day 열의 값입니다. 중복 된 것으로 간주하면 여기에 해당 항목을 찾는 쿼리가 있습니다. 쿼리는 GROUP BY 절을 사용하여 동일한 day 값을 가진 모든 행을 하나의 “그룹”에 넣은 다음 group :
중복 된 행의 개수가 1보다 많습니다. 중복 된 행만 보려면
절 (WHERE 절이 아님) :
이것이 기본 기술입니다. 중복 항목이 포함 된 열을 기준으로 그룹화하고 행이 두 개 이상인 그룹 만 표시합니다.
WHERE 절을 사용할 수없는 이유는 무엇입니까?
A WHERE 절은 함께 그룹화되기 전에 행을 필터링합니다. HAVING 절은 그룹화 후 필터링합니다. 따라서 WHERE 절.
중복 행 삭제 방법
관련 질문은 ‘중복’행을 삭제하는 방법입니다. 그들을 찾으십시오. 잘못된 데이터를 정리할 때 요청하는 것은 중복 항목 중 하나를 제외한 모든 항목을 삭제하는 것이므로 테이블에 적절한 인덱스와 기본 키를 배치하고 중복 항목이 다시 테이블에 들어가는 것을 방지 할 수 있습니다.
다시 한 번 말씀 드리겠습니다. 해야 할 일은 당신의 정의가 명확한 지 확인하는 것입니다. 정확히 어떤 행을 유지 하시겠습니까? 첫번째? 어떤 열의 가장 큰 값을 가진 것? 이 기사에서는 id 열의 값이 가장 작은 ‘첫 번째’행을 유지한다고 가정합니다. 즉, 다른 모든 행을 삭제하려고합니다.
이 작업을 수행하는 가장 쉬운 방법은 임시 테이블을 사용하는 것입니다. 특히 MySQL에서는 테이블에서 선택하고 동일한 쿼리에서 업데이트하는 데 몇 가지 제한이 있습니다. MySQL의 업데이트 대상에서 선택하는 방법에 설명 된대로 이러한 문제를 해결할 수 있지만 이러한 문제를 피하고 임시 테이블을 사용하겠습니다.
작업의 정확한 정의는 다음과 같습니다. 해당 그룹에 대해 최소값이 id 인 행을 제외하고 중복 된 모든 행을 삭제합니다. 따라서 그룹에 둘 이상의 행이있는 행을 찾아야 할뿐만 아니라 유지하려는 행도 찾아야합니다. MIN() 함수를 사용하면됩니다. 다음은 임시 테이블을 만들고 DELETE를 수행하는 데 필요한 데이터를 찾는 몇 가지 쿼리입니다.
이제이 데이터가 있으므로 삭제를 진행할 수 있습니다. ‘나쁜’행. 이를 수행하는 방법에는 여러 가지가 있으며 일부는 다른 것보다 낫지 만 (SQL의 다 대일 문제에 대한 내 기사 참조) 다시 한 번 더 세밀한 요점을 피하고 작동해야하는 표준 구문을 보여줄 것입니다. 하위 쿼리를 지원하는 모든 RDBMS :
RDBMS가 하위 쿼리를 지원하지 않거나 더 효율적인 경우 다중 테이블 삭제를 수행 할 수 있습니다. 이에 대한 구문은 시스템마다 다르므로 시스템 설명서를 참조해야합니다. 문제가되는 경우 작업하는 동안 다른 사용자가 데이터를 변경하지 않도록 트랜잭션에서이 모든 작업을 수행해야 할 수도 있습니다.
여러 열에서 중복 항목을 찾는 방법
누군가 최근 #mysql IRC 채널에서 다음과 유사한 질문을했습니다.
b<열이있는 테이블이 있습니다. 두 개의 다른 테이블b및c를 연결하는 / div> 및c.b또는c에서 중복 된 모든 행을 찾습니다.
이게 무슨 뜻인지 정확히 이해하기 어려웠지만 대화를 나누고 나서 파악했습니다. 그 사람은 b 및 c 개별적으로.
위에서 보여 드린 것처럼 한 열 또는 다른 열에서 중복 값이있는 행을 찾는 것은 매우 쉽습니다. 해당 열로 그룹화하면됩니다. mn 및 그룹 크기를 세십시오. 또한 다른 행과 정확히 중복되는 전체 행을 쉽게 찾을 수 있습니다. 필요한만큼 열을 기준으로 그룹화하기 만하면됩니다.그러나 중복 된 b 값 또는 중복 된 c 값이있는 행을 식별하는 것은 더 어렵습니다. 사람이 대략적으로 설명한 다음 샘플 테이블을 가져옵니다.
이제이 테이블에 ‘중복’행이 있지만 실제로 동일한 튜플이없는 두 행이 있음을 쉽게 알 수 있습니다. {b, c}. 이것이 해결하기가 조금 더 어려운 이유입니다.
작동하지 않는 쿼리
두 개의 열로 그룹화하면 그룹화 방법에 따라 다양한 결과를 얻을 수 있습니다. 그리고 카운트. IRC 사용자가 당황한 곳입니다. 때때로 쿼리는 일부 중복을 찾지 만 다른 것은 찾지 못합니다. 이 사람이 시도한 몇 가지 작업은 다음과 같습니다.
이 쿼리는 COUNT(*) of 1, 잘못된 동작으로 보이지만 실제로는 그렇지 않습니다. 왜? > 1는 COUNT() 내부에 있기 때문입니다. 놓치기 쉽지만이 검색어는 실제로
이유와 동일합니다. (b > 1)는 부울 표현식이기 때문입니다. 그것은 당신이 원하는 것이 아닙니다. 당신은
중복 된 {b, c} 튜플이 없기 때문에 0 개의 행을 반환합니다. 그 사람은 HAVING 절과 OR 및 AND의 다양한 조합을 시도하여 한 열로 그룹화하고 다른 열을 세는 등의 작업을 시도했습니다.
하지만 모든 중복을 찾지 못했습니다. 제가 생각하기에 가장 실망 스러웠던 점은 부분적으로 작동하여 거의 올바른 쿼리라고 생각하게 만든다는 것입니다. 아마도 다른 변형으로 얻을 수있을 것입니다…
사실, 이러한 유형으로 수행하는 것은 불가능합니다. 간단한 GROUP BY 쿼리입니다. 왜 이런거야? 하나의 열로 그룹화하면 다른 열의 값처럼 여러 그룹에 분산되기 때문입니다. 이러한 열을 기준으로 정렬하면 시각적으로 확인할 수 있습니다. 먼저 b 열을 기준으로 정렬하고 그룹화 방식을 확인합니다.
| a | b | c |
|---|---|---|
| 7 | 1 | 1 |
| 8 | 1 | 2 |
| 9 | 1 | 3 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
| 12 | 2 | 3 |
| 13 | 3 | 1 |
| 14 | 3 | 2 |
| 15 | 3 | 3 |
b 열을 기준으로 정렬 (그룹화)하면 c는 서로 다른 그룹으로 나뉘어져 있으므로 COUNT(DISTINCT c)로 계산할 수 없습니다. COUNT()와 같은 집계 함수는 그룹 내에서만 작동하며 다른 그룹에있는 행에는 액세스 할 수 없습니다. 마찬가지로 c로 주문하면 b 열의 중복 값이 다른 그룹으로 배포됩니다. 이 쿼리가 원하는 작업을 수행하도록 할 수 없습니다.
일부 올바른 솔루션
아마 가장 간단한 솔루션은 각 열에 대한 중복을 개별적으로 찾고 UNION 함께 다음과 같이합니다.
출력의 what_col 열은 중복 값이 발견 된 열을 나타냅니다. 또 다른 접근 방식은 하위 쿼리 :
이 방법은 UNION 접근 방식보다 훨씬 덜 효율적이며 중복 된 값뿐만 아니라 모든 중복 된 행을 표시합니다. 또 다른 접근 방식은 FROM 절의 그룹화 된 하위 쿼리에 대해 자체 조인을 수행하는 것입니다. 이는 올바르게 작성하기가 더 복잡하지만 복잡한 데이터 나 효율성을 위해 필요할 수 있습니다.
이러한 쿼리는 모두 가능하며 다른 방법도있을 것입니다. UNION를 사용할 수 있다면 아마도 가장 쉬운 방법 일 것입니다.
