잡개발자 리토의 잡위키 잡개발자 리토의 잡위키

사용자 도구

사이트 도구

사이드바

기술문서:레퍼런스:쿼리:응용:recursive_query

문서의 이전 판입니다!

Recursive Query

최소 지원 버전은 약간의 차이가 있을 수 있습니다.
물리명 논리명 타입 KEY
코드ID cd_id 문자형 PK
코드명 cd_nm 문자형
부모 코드ID parent_cd_id 문자형
정렬 순서 sort_order 숫자형 

-- MySQL/MariaDB & MSSQL & PostgreSQL
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 BIGINT NOT NULL
);

-- Oracle/Tibero
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 NUMBER 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 이상/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 a.parent_cd_id = b.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 a.parent_cd_id = b.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 a.parent_cd_id = b.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 a.parent_cd_id = b.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 a.parent_cd_id = b.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 a.parent_cd_id = b.cd_id
)
SEARCH DEPTH FIRST BY sort_order SET seq
SELECT
	*
FROM
	tree
ORDER BY
	seq ASC
;

문서 도구