Post

중복 레코드 개수 확인

1. 테이블

1
2
3
4
5
6
7
8
9
10
11
12
13
14
sql> SELECT id, str FROM str_table;

+----+-----+
| id | str |
+----+-----+
|  1 | AAA |
|  2 | BBB |
|  3 | CCC |
|  4 | AAA |
|  5 | CCC |
|  6 | DDD |
|  7 | EEE |
|  8 | AAA |
+----+-----+

2. 모든 레코드 개수

SELECT 컬럼명, COUNT(*) FROM 테이블명 GROUP BY 컬럼명;

1
2
3
4
5
6
7
8
9
10
11
sql> SELECT str, COUNT(*) FROM str_table GROUP BY str;

+-----+----------+
| str | COUNT(*) |
+-----+----------+
| AAA |        3 |
| BBB |        1 |
| CCC |        2 |
| DDD |        1 |
| EEE |        1 |
+-----+----------+

3. 중복 레코드 개수

SELECT 컬럼명, COUNT(*) FROM 테이블명 GROUP BY 컬럼명 HAVING COUNT(*) > 1;

1
2
3
4
5
6
7
8
sql> SELECT str, COUNT(*) FROM str_table GROUP BY str HAVING COUNT(*) > 1;

+-----+----------+
| str | COUNT(*) |
+-----+----------+
| AAA |        3 |
| CCC |        2 |  
+-----+----------+

[출처 및 참고]

This post is licensed under CC BY 4.0 by the author.