[MySQL] 빠진 번호 찾기

안녕하세요.

오늘은 일련번호나 인덱스에서 빠진 번호를 찾아야 할 때 유용한 MySQL 쿼리문에 대해 알아보겠습니다.

 

 MySQL 빠진 번호 찾기

▣ 예시 데이터 :: Example_No

No	Name	Age
1	Sam	29
2	John	37
4	Key	28
8	Pow	65
11	Kane	21
12	Jane	23
19	Sol	28

CREATE TABLE Example_No
    (`No` int, `Name` varchar(4), `Age` int)
;
    
INSERT INTO Example_No
    (`No`, `Name`, `Age`)
VALUES
    (1, 'Sam', 29),
    (2, 'John', 37),
    (4, 'Key', 28),
    (8, 'Pow', 65),
    (11, 'Kane', 21),
    (12, 'Jane', 23),
    (19, 'Sol', 28)
;

 

▣ 빠진 번호 일괄 찾기

--빠진 번호 맞추기 위한 숫자 테이블 cte 생성
CREATE TABLE cte
    (`num` int)
;
    
INSERT INTO cte
    (`num`)
VALUES
    (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
    (11), (12), (13), (14), (15), (16), (17), (18), (19)
;

--순차적으로 빠진 번호 일괄 찾기
SELECT A.num
FROM cte A LEFT JOIN Example_No B on (B.No = A.num)
WHERE B.No IS NULL
AND A.num <= (SELECT  MAX(C.No) FROM Example_No C);

결과 조회

- 빠진 번호는 총 12개로 조회되었으며, 빠진 번호 조회 시 사용하는 테이블을 만들 경우에는 with문이나 DUAL과 같은 더미 테이블을 사용하여도 됨. (추후 임시 숫자 테이블을 만드는 방법에 대해서도 포스팅 예정)

 

▣ 빠진 번호 최솟값, 최댓값 조회

--빠진 번호 최솟값 찾기
SELECT min(No+1) AS Missing_No_min
FROM Example_No
WHERE (No+1) NOT IN (SELECT No FROM Example_No)

--빠진 번호 최댓값 찾기
SELECT max(No-1) AS Missing_No_max
FROM Example_No
WHERE (No-1) NOT IN (SELECT No FROM Example_No)

결과 조회

 

▣ 빠진 번호 범위로 표현하기

--빠진 번호 범위로 나누기
# CONCAT으로 범위 '~' 표현
SELECT CONCAT(X.EXPECTED, IF(X.GOT-1>X.EXPECTED, CONCAT(' ~ ',X.GOT-1), '')) AS Missing_Num_Range
FROM ( SELECT @ROWNUM:=@ROWNUM+1 AS EXPECTED,
       IF(@ROWNUM=B.No, 0, @ROWNUM:=B.No) AS GOT
       FROM (SELECT @ROWNUM:=0) AS A 
       INNER JOIN Example_No B
       ORDER BY B.No 
      ) AS X
WHERE X.GOT!=0;

결과 조회

- @ROWNUM 변수 선언을 통해 숫자 테이블을 만들고 CONCAT으로 빠진 번호를 범위로 표현함.

 

Reference

• [SQL] MariaDB/MySQL 테이블 일련번호 중간에 빈 값 찾기