Recursive Query

최소 지원 버전은 약간의 차이가 있을 수 있습니다.

테이블 명세

물리명	논리명	타입	길이	KEY
코드ID	cd_id	문자형	100	PK
코드명	cd_nm	문자형	100
부모 코드ID	parent_cd_id	문자형	100
정렬 순서	sort_order	숫자형

SQL 스크립트

CREATE TABLE ex_recursive (
	cd_id VARCHAR(100) NOT NULL PRIMARY KEY,
	cd_nm VARCHAR(100) NOT NULL,
	parent_cd_id VARCHAR(100),
	sort_order INT NOT NULL
);

INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_01', '1', NULL, 1);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_01_01', '1.1', 'CD_01', 1);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_01_01_01', '1.1.1', 'CD_01_01', 1);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_01_01_02', '1.1.2', 'CD_01_01', 2);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_01_02', '1.2', 'CD_01', 2);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_01_02_01', '1.2.1', 'CD_01_02', 1);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_01_02_02', '1.2.2', 'CD_01_02', 2);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_02', '2', NULL, 2);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_02_01', '2.1', 'CD_02', 1);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_02_01_01', '2.1.1', 'CD_02_01', 1);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_02_01_02', '2.1.2', 'CD_02_01', 2);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_02_02', '2.2', 'CD_02', 2);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_02_02_01', '2.2.1', 'CD_02_02', 1);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_02_02_02', '2.2.2', 'CD_02_02', 2);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_99', '99', NULL, 99);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_99_01', '99.1', 'CD_99', 1);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_99_01_01', '99.1.1', 'CD_99_01', 1);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_99_01_02', '99.1.2', 'CD_99_01', 2);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_99_02', '99.2', 'CD_99', 2);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_99_02_01', '99.2.1', 'CD_99_02', 1);
INSERT INTO ex_recursive (cd_id, cd_nm, parent_cd_id, sort_order) VALUES ('CD_99_02_02', '99.2.2', 'CD_99_02', 2);

MySQL/MariaDB

/*
	MySQL 8.0.1 이상/MariaDB 10.2.2 이상
*/
WITH RECURSIVE tree (cd_id, cd_nm, parent_cd_id, sort_order, cd_lvl, cd_nm_depth, seq) AS (
	SELECT
		cd_id
		, cd_nm
		, parent_cd_id
		, sort_order
		, 1
		, cd_nm
		, CAST(LPAD(CAST(sort_order AS CHAR), 3, '0') AS CHAR(255)) -- 정렬 순서 3자리(999)/64단계까지 지원
		-- , CAST(LPAD(CAST(sort_order AS CHAR), 4, '0') AS CHAR(255)) -- 정렬 순서 4자리(9999)/51단계까지 지원
		-- , CAST(LPAD(CAST(sort_order AS CHAR), 5, '0') AS CHAR(255)) -- 정렬 순서 5자리(99999)/42단계까지 지원
	FROM
		ex_recursive
	WHERE
		parent_cd_id IS NULL
	UNION ALL
	SELECT
		 a.cd_id
		, a.cd_nm
		, a.parent_cd_id
		, a.sort_order
		, b.cd_lvl + 1
		, CONCAT(b.cd_nm_depth, ' > ', a.cd_nm)
		, CAST(CONCAT(b.seq, ':', LPAD(CAST(a.sort_order AS CHAR), 3, '0')) AS CHAR(255)) -- 정렬 순서 3자리(999)/64단계까지 지원
		-- , CAST(CONCAT(b.seq, ':', LPAD(CAST(a.sort_order AS CHAR), 4, '0')) AS CHAR(255)) -- 정렬 순서 4자리(9999)/51단계까지 지원
		-- , CAST(CONCAT(b.seq, ':', LPAD(CAST(a.sort_order AS CHAR), 5, '0')) AS CHAR(255)) -- 정렬 순서 5자리(99999)/42단계까지 지원
	FROM
		ex_recursive a
		INNER JOIN tree b
			ON b.cd_id = a.parent_cd_id
)
SELECT
	*
FROM
	tree
ORDER BY
	seq ASC
;

MSSQL

/*
	MSSQL 2005 이상
*/
WITH tree (cd_id, cd_nm, parent_cd_id, sort_order, cd_lvl, cd_nm_depth, seq) AS (
	SELECT
		cd_id
		, cd_nm
		, parent_cd_id
		, sort_order
		, 1
		, CAST(cd_nm AS NVARCHAR)
		, REPLICATE('0', 3 - LEN(sort_order)) + CAST(sort_order AS VARCHAR) -- 정렬 순서 3자리(999)까지 지원
		-- , REPLICATE('0', 4 - LEN(sort_order)) + CAST(sort_order AS VARCHAR) -- 정렬 순서 4자리(9999)까지 지원
		-- , REPLICATE('0', 5 - LEN(sort_order)) + CAST(sort_order AS VARCHAR) -- 정렬 순서 5자리(99999)까지 지원
	FROM
		ex_recursive
	WHERE
		parent_cd_id IS NULL
	UNION ALL
	SELECT
		 a.cd_id
		, a.cd_nm
		, a.parent_cd_id
		, a.sort_order
		, b.cd_lvl + 1
		, CAST((b.cd_nm_depth + ' > ' + a.cd_nm) AS NVARCHAR)
		, b.seq + ':' + REPLICATE('0', 3 - LEN(a.sort_order)) + CAST(a.sort_order AS VARCHAR) -- 정렬 순서 3자리(999)까지 지원
		-- , b.seq + ':' + REPLICATE('0', 4 - LEN(a.sort_order)) + CAST(a.sort_order AS VARCHAR) -- 정렬 순서 4자리(9999)까지 지원
		-- , b.seq + ':' + REPLICATE('0', 5 - LEN(a.sort_order)) + CAST(a.sort_order AS VARCHAR) -- 정렬 순서 5자리(99999)까지 지원
	FROM
		ex_recursive a
		INNER JOIN tree b
			ON b.cd_id = a.parent_cd_id
)
SELECT
	*
FROM
	tree
ORDER BY
	seq ASC
;

Oracle/Tibero

/*
	Oracle 9i 이상/Tibero 3 이상
*/
SELECT
	cd_id
	, cd_nm
	, parent_cd_id
	, sort_order
	, LEVEL AS cd_lvl
	, LTRIM(SYS_CONNECT_BY_PATH(cd_nm, ' > '), ' > ') AS cd_nm_depth
FROM
	ex_recursive
START WITH
	parent_cd_id IS NULL
CONNECT BY PRIOR
-- CONNECT BY NOCYCLE PRIOR -- Oracle 10g 이상
	cd_id = parent_cd_id
ORDER SIBLINGS BY
	sort_order ASC
;

/*
	Oracle 11g 이상/Tibero 3 이상
*/
WITH tree (cd_id, cd_nm, parent_cd_id, sort_order, cd_lvl, cd_nm_depth, seq) AS (
	SELECT
		cd_id
		, cd_nm
		, parent_cd_id
		, sort_order
		, 1
		, '' || cd_nm
		, LPAD(sort_order, 3, '0') -- 정렬 순서 3자리(999)까지 지원
		-- , LPAD(sort_order, 4, '0') -- 정렬 순서 4자리(9999)까지 지원
		-- , LPAD(sort_order, 5, '0') -- 정렬 순서 5자리(99999)까지 지원
	FROM
		ex_recursive
	WHERE
		parent_cd_id IS NULL
	UNION ALL
	SELECT
		 a.cd_id
		, a.cd_nm
		, a.parent_cd_id
		, a.sort_order
		, b.cd_lvl + 1
		, b.cd_nm_depth || ' > ' || a.cd_nm
		, b.seq || ':' || LPAD(a.sort_order, 3, '0') -- 정렬 순서 3자리(999)까지 지원
		-- , b.seq || ':' || LPAD(a.sort_order, 4, '0') -- 정렬 순서 4자리(9999)까지 지원
		-- , b.seq || ':' || LPAD(a.sort_order, 5, '0') -- 정렬 순서 5자리(99999)까지 지원
	FROM
		ex_recursive a
		INNER JOIN tree b
			ON b.cd_id = a.parent_cd_id
)
SELECT
	*
FROM
	tree
ORDER BY
	seq ASC
;

/*
	Oracle 12c 이상
*/
WITH tree (cd_id, cd_nm, parent_cd_id, sort_order, cd_lvl, cd_nm_depth) AS (
	SELECT
		cd_id
		, cd_nm
		, parent_cd_id
		, sort_order
		, 1
		, '' || cd_nm
	FROM
		ex_recursive
	WHERE
		parent_cd_id IS NULL
	UNION ALL
	SELECT
		 a.cd_id
		, a.cd_nm
		, a.parent_cd_id
		, a.sort_order
		, b.cd_lvl + 1
		, b.cd_nm_depth || ' > ' || a.cd_nm
	FROM
		ex_recursive a
		INNER JOIN tree b
			ON b.cd_id = a.parent_cd_id
)
SEARCH DEPTH FIRST BY sort_order SET seq
SELECT
	*
FROM
	tree
ORDER BY
	seq ASC
;

PostgreSQL

/*
	PostgreSQL 8.4 이상
*/
WITH RECURSIVE tree (cd_id, cd_nm, parent_cd_id, sort_order, cd_lvl, cd_nm_depth, seq) AS (
	SELECT
		cd_id
		, cd_nm
		, parent_cd_id
		, sort_order
		, 1
  		, CAST(cd_nm AS VARCHAR)
		, ARRAY[sort_order]
	FROM
		ex_recursive
	WHERE
		parent_cd_id IS NULL
	UNION ALL
	SELECT
		 a.cd_id
		, a.cd_nm
		, a.parent_cd_id
		, a.sort_order
		, b.cd_lvl + 1
		, CAST((b.cd_nm_depth || ' > ' || a.cd_nm) AS VARCHAR)
		, b.seq || ARRAY[a.sort_order]
	FROM
		ex_recursive a
		INNER JOIN tree b
			ON b.cd_id = a.parent_cd_id
)
SELECT
	*
FROM
	tree
ORDER BY
	seq ASC
;

/*
	PostgreSQL 14 이상
*/
WITH RECURSIVE tree (cd_id, cd_nm, parent_cd_id, sort_order, cd_lvl, cd_nm_depth) AS (
	SELECT
		cd_id
		, cd_nm
		, parent_cd_id
		, sort_order
		, 1
  		, CAST(cd_nm AS VARCHAR)
	FROM
		ex_recursive
	WHERE
		parent_cd_id IS NULL
	UNION ALL
	SELECT
		 a.cd_id
		, a.cd_nm
		, a.parent_cd_id
		, a.sort_order
		, b.cd_lvl + 1
		, CAST((b.cd_nm_depth || ' > ' || a.cd_nm) AS VARCHAR)
	FROM
		ex_recursive a
		INNER JOIN tree b
			ON b.cd_id = a.parent_cd_id
)
SEARCH DEPTH FIRST BY sort_order SET seq
SELECT
	*
FROM
	tree
ORDER BY
	seq ASC
;

쿼리 Query 응용