잡개발자 리토의 잡위키

이 문서는 읽기 전용입니다. 원본을 볼 수는 있지만 바꿀 수는 없습니다. 문제가 있다고 생각하면 관리자에게 문의하세요.
{{htmlmetatags>
metatag-description=(재귀 쿼리, Recursive Query, 재귀, Recursive, 데이터베이스, DBMS, SQL, MySQL, MariaDB, MSSQL, Oracle, Tibero, PostgreSQL)
metatag-og:description=(재귀 쿼리, Recursive Query, 재귀, Recursive, 데이터베이스, DBMS, SQL, MySQL, MariaDB, MSSQL, Oracle, Tibero, PostgreSQL)
}}

====== Recursive Query ======

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

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

===== SQL 스크립트 =====
<sxh sql; gutter: true; title: DDL;>
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
);
</sxh>

<sxh sql; gutter: true; title: DML;>
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);
</sxh>

===== MySQL/MariaDB =====
<sxh sql; gutter: true;>
/*
	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
;
</sxh>

===== MSSQL =====
<sxh sql; gutter: true;>
/*
	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
;
</sxh>

===== Oracle/Tibero =====
<sxh sql; gutter: true; highlight: [16, 92];>
/*
	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
;
</sxh>

===== PostgreSQL =====
<sxh sql; gutter: true; highlight: [67];>
/*
	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
;
</sxh>

{{tag>"쿼리" "Query" "응용"}}
잡개발자 리토의 잡위키

사용자 도구

사이트 도구

문서 도구
