WITH RECURSIVE, UNION을 사용하여 계층구조 질의를 작성하기 위한 기초 정리
원문참조 : https://dev.mysql.com/doc/refman/8.0/en/with.html
MySQL의 WITH 절은 WITH절에 포함된 서브커리의 결과집합을 일단 이름을 붙여서 메모리에 저장하고 WITH 절 외부의 메인 커리에서 그 이름을 이용하여 마치 테이블처럼 사용할 수 있다. 그런 점에서 가상의 테이블이라고 설명할 수도 있다
# UNION 결과 : 1,2 출력
SELECT 1 AS num
UNION
SELECT 2 AS num;
# WITH, UNION 결과 : 1,2 출력
WITH test AS
(
SELECT 1 AS num
UNION ALL
SELECT 2 AS num
)
SELECT * FROM test;
# WITH, UNION 결과 : 1,2,2,2,... (emp테이블의 행수만큼 2가 반복됨)
WITH test AS
(
SELECT 1 AS num
UNION ALL
SELECT 2 AS num FROM emp # 이 문장은 emp 테이블의 행수만큼 반복된다
)
SELECT * FROM test;
# 서브커리에서 바깥 WITH 테이블 참조. 결과: RECURSIVE 가 없으므로 오류발생
WITH test AS
(
SELECT 1 AS num
UNION
SELECT num+1 AS num FROM test WHERE num<10
)
SELECT * FROM test;
# 내부에 UNION 이 없으므로 오류
# Recursive Common Table Expression은 UNION을 포함해야 함
WITH RECURSIVE test AS
(
SELECT num+1 AS num FROM test WHERE num<10
)
SELECT * FROM test;
# RECURSIVE 문장에 정지조건이 없으므로 오류
WITH RECURSIVE test AS
(
SELECT 1 AS num
UNION
SELECT num+1 AS num FROM test
)
SELECT * FROM test;
# 오류가 없는 정상적인 문장
# Recursive 내부에 UNION을 사용하고 정지조건도 사용한 경우
WITH RECURSIVE test AS
(
SELECT 1 AS num
UNION
SELECT num+1 AS num FROM test WHERE num<10
)
SELECT * FROM test;
# Recursive 내부에는 한개 이상의 Non-Recursive문장이 포함되어야 하므로 아래의 문장은 오류
# Non-Recursive 문장이란 내부에서 바깥 WITH RECURSIVE 테이블을 참조하지 않는 문장을 의미함
WITH RECURSIVE test AS
(
SELECT 1 AS num FROM test WHERE num<10
UNION
SELECT num+1 AS num FROM test WHERE num<10
)
SELECT * FROM test;
WITH RECURSIVE test AS
( # Non-Recursive 문장( 첫번째 루프에서만 실행됨 )
SELECT 1 AS num FROM emp # 첫 루프에서 emp 테이블의 행 수만큼 자체 반복됨
UNION ALL
# Recursive 문장(읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함)
SELECT num+1 AS num FROM test WHERE num<10
)
SELECT * FROM test;
다수개의 결과집합에 대한 이름을 붙일 때는 다음과 같은 형식을 사용한다
WITH name1 AS
(
SELECT ~ (Sub Query)
),
name2 AS
(
SELECT ~ (Sub Query)
)
SELECT ~ (Main Query)
WITH RECURSIVE 관련 정리
- 메모리 상에 가상의 테이블을 저장
- 반드시 UNION 사용
- 반드시 비반복문도 최소한 1개 요구됨
- 서브커리에서 바깥의 가상의 테이블을 참조하는 문장(반복문)이 반드시 필요함
- 반복되는 문장은 반드시 정지조건이 요구됨
- 가상의 테이블을 구성하면서 그 자신(가상의 테이블)을 참조하여 값을 결정할 때 유용함
상세한 해석 연습
아래의 문장에서 사용된 emp 테이블에는 총 10개의 행이 저장되어 있다고 가정한다
아래의 문장을 MySQL8에서 실행하면 1~10 사이의 숫자가 순서대로 10개씩 총 100개 출력된다
왜 그러한 결과가 나오는지 논리적으로 설명하고 그림으로 그릴 수 있는 정도까지 연습해보기 바란다
WITH RECURSIVE test AS
( # Non-Recursive 문장( 첫번째 루프에서만 실행됨 )
SELECT 1 AS num FROM emp # 첫 루프에서 emp 테이블의 행 수만큼 자체 반복됨
UNION ALL
# Recursive 문장(읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함)
SELECT num+1 AS num FROM test WHERE num<10
)
SELECT * FROM test;
아래의 계층구조 질의에서 사용할 함수
REPEAT(str, count)
- str 문자열을 count 수만큼 반복하여 결합한다
- SELECT REPEAT(' * ', 10);
CONCAT(exp1, exp2, ......)
- 2개 이상의 파라미터를 문자열로 결합한다
- 첫번째 파라미터는 필수이다
SELECT CONCAT ( REPEAT( ' ', 10 ), title ) AS title FROM bbs;
CREATE TABLE emp
(
id INT AUTO_INCREMENT PRIMARY KEY,
ename VARCHAR(255),
parent_id INT DEFAULT 0 REFERENCES id
);
INSERT INTO emp VALUES
(1, 'name1', 0),
(2, 'name2', 1),
(3, 'name3', 1),
(4, 'name4', 3),
(5, 'name5', 0),
(6, 'name6', 3),
(7, 'name7', 4),
(8, 'name8', 5),
(9, 'name9', 8),
(10, 'name10',8);
WITH RECURSIVE tmp1 AS
(
SELECT id, ename, parent_id,
ename AS path, 1 AS lvl
FROM emp WHERE parent_id=0
UNION ALL
SELECT e.id, e.ename, e.parent_id,
CONCAT(t.path,',',e.ename) AS path, t.lvl+1 AS lvl
FROM tmp1 t JOIN emp e ON t.id=e.parent_id
)
SELECT id, CONCAT(REPEAT(' ', lvl*4), ename) ename, parent_id, path, lvl
FROM tmp1
ORDER BY path; # path 임시컬럼을 사용하여 정렬하면 계층구조로 정렬된다
Workbench에서 위의 문장을 실행한 결과
