[mssql] 트리 구조의 데이터에서 자식노드(하위노드)의 부모노드(상위노드) 찾는 방법(재귀 쿼리)

떠돌이개발자Jun 21, 2023

트리구조로 구성된 테이블의 데이터에서 자식노드가 속한 부모노드를 모두 찾는 함수를 구현하였다.

MS-SQL 2005부터 사용가능해진 재귀쿼리를 이용하여 트리구조의 데이터에 대한 어떤 값을 찾아올 때 유용하다.

WITH TEMP AS (
	SELECT 컬럼 FROM 테이블명
	UNION ALL
	SELECT 컬럼 FROM 테이블명
	INNER JOIN TEMP ON 테이블명.컬럼 = TEMP.컬럼
)

CREATE FUNCTION FN_GET_P_NODE
(
	@DEPT_ID	BIGINT
)
RETURNS 
@TB_TREE_DEPT TABLE 
(
	DEPT_ID			BIGINT
,	PARENT_DEPT_ID	BIGINT
)
AS
BEGIN

	WITH CTE_DEPT AS
	(
		SELECT	DEPT_ID
			,	PARENT_DEPT_ID
		FROM TBL_USER_DEPT
		WHERE DEPT_ID = @DEPT_ID
		UNION ALL
		SELECT	dept.DEPT_ID
			,	dept.PARENT_DEPT_ID
		FROM TBL_USER_DEPT dept
		INNER JOIN CTE_DEPT cte ON cte.PARENT_DEPT_ID = dept.DEPT_ID
	)
	INSERT INTO @TB_TREE_DEPT
	(
		DEPT_ID
	,	PARENT_DEPT_ID
	)
	SELECT
		DEPT_ID
	,	PARENT_DEPT_ID
	FROM CTE_DEPT

	RETURN;
END

이와 반대로 부모노드에 속한 모든 자식 노드를 찾는 함수는 다음과 같다.

차이점은 하나이다.

INNER JOIN CTE_DEPT cte ON cte.PARENT_DEPT_ID = dept.DEPT_ID 조인을

INNER JOIN CTE_DEPT cte ON cte.DEPT_ID = dept.PARENT_DEPT_ID 로 변경하면 된다.

CREATE FUNCTION FN_GET_CHILD_NODE
(
	@DEPT_ID	BIGINT
)
RETURNS 
@TB_TREE_DEPT TABLE 
(
	DEPT_ID			BIGINT
,	PARENT_DEPT_ID	BIGINT
)
AS
BEGIN

	WITH CTE_DEPT AS
	(
		SELECT	DEPT_ID
			,	PARENT_DEPT_ID
		FROM TBL_USER_DEPT
		WHERE DEPT_ID = @DEPT_ID
		UNION ALL
		SELECT	dept.DEPT_ID
			,	dept.PARENT_DEPT_ID
		FROM TBL_USER_DEPT dept
		INNER JOIN CTE_DEPT cte ON cte.DEPT_ID = dept.PARENT_DEPT_ID
	)
	INSERT INTO @TB_TREE_DEPT
	(
		DEPT_ID
	,	PARENT_DEPT_ID
	)
	SELECT
		DEPT_ID
	,	PARENT_DEPT_ID
	FROM CTE_DEPT

	RETURN;
END

사용방법은 다음과 같다. 테이블 데이터를 반환함으로 FROM절로 사용할 수 있고 다른 테이블과 조인할 수 있다.