재귀 쿼리로 조직 구조 조회하기 (WITH 사용, Recursive Query)

SQL Server에서 재귀 쿼리 사용하여 조직 구조 조회하기

데이터베이스를 다루다 보면 계층적 데이터를 처리해야 하는 경우가 자주 발생합니다. 예를 들어, 조직의 직원 구조나 파일 시스템의 디렉터리 트리를 생각할 수 있습니다. SQL Server에서 이러한 계층적 데이터를 조회하기 위해 재귀 쿼리를 사용할 수 있습니다. 이번 포스트에서는 WITH 절을 사용한 공통 테이블 표현식(CTE, Common Table Expression)을 활용하여 조직 구조를 재귀적으로 조회하는 방법을 살펴보겠습니다.

예제 데이터 준비

먼저, 예제 데이터를 저장할 Employees 테이블을 생성하고, 조직 구조를 나타내는 데이터를 삽입하겠습니다.

-- 직원 테이블 생성
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(50),
    ManagerID INT NULL
);

-- 예시 데이터 삽입
INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) VALUES
(1, 'CEO', NULL),
(2, 'Manager1', 1),
(3, 'Manager2', 1),
(4, 'Employee1', 2),
(5, 'Employee2', 2),
(6, 'Employee3', 3),
(7, 'Employee4', 3);

위의 데이터를 사용하여 조직 구조를 재귀적으로 쿼리하는 예제를 보겠습니다.

재귀 쿼리 작성

다음은 재귀 쿼리를 사용하여 조직 구조를 조회하는 예제입니다.

WITH EmployeeHierarchy AS (
    -- 앵커 멤버 정의: 최상위 관리자(CEO)를 선택
    SELECT 
        EmployeeID,
        EmployeeName,
        ManagerID,
        CAST(EmployeeName AS NVARCHAR(MAX)) AS HierarchyPath,
        0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- 재귀 멤버 정의: 하위 직원을 선택
    SELECT 
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        CAST(eh.HierarchyPath + ' -> ' + e.EmployeeName AS NVARCHAR(MAX)) AS HierarchyPath,
        eh.Level + 1 AS Level
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
-- 최종 결과 선택
SELECT 
    EmployeeID,
    EmployeeName,
    ManagerID,
    HierarchyPath,
    Level
FROM EmployeeHierarchy
ORDER BY HierarchyPath;

이 쿼리는 조직의 계층 구조를 트리 형태로 반환합니다. HierarchyPath 열은 각 직원이 속한 경로를 문자열로 보여주고, Level 열은 직원의 계층 레벨을 나타냅니다.

결과 보기

위의 재귀 쿼리를 실행하면 다음과 같은 결과가 나옵니다:

EmployeeID	EmployeeName	ManagerID	HierarchyPath	Level
1	CEO	NULL	CEO	0
2	Manager1	1	CEO -> Manager1	1
4	Employee1	2	CEO -> Manager1 -> Employee1	2
5	Employee2	2	CEO -> Manager1 -> Employee2	2
3	Manager2	1	CEO -> Manager2	1
6	Employee3	3	CEO -> Manager2 -> Employee3	2
7	Employee4	3	CEO -> Manager2 -> Employee4	2

이 표는 조직의 계층 구조를 보여줍니다:

• 최상위 CEO는 Level 0에 있습니다.
• CEO 아래 Manager1과 Manager2가 Level 1에 있습니다.
• Manager1 아래 Employee1과 Employee2가 Level 2에 있습니다.
• Manager2 아래 Employee3과 Employee4가 Level 2에 있습니다.

HierarchyPath 열은 각 직원의 경로를 나타내며, Level 열은 조직 계층의 깊이를 나타냅니다.

반응형

혹시 재귀함수가 어렵나요?

이해가 어려운 분들을 위해 1~10까지의 수를 재귀함수를 이용해 작성했습니다.

WITH Numbers AS (
    -- 앵커 멤버: 숫자 1로 시작
    SELECT 1 AS Number

    UNION ALL

    -- 재귀 멤버: 숫자에 1을 더해서 10에 도달할 때까지 반복
    SELECT Number + 1
    FROM Numbers
    WHERE Number < 10
)
-- 재귀 CTE에서 모든 숫자를 선택
SELECT Number
FROM Numbers;

마무리

SQL Server의 재귀 쿼리를 사용하면 복잡한 계층적 데이터를 손쉽게 조회할 수 있습니다. 조직 구조, 디렉터리 트리, 조상-후손 관계 등의 다양한 계층적 데이터를 처리할 때 매우 유용합니다. 이 글에서 소개한 예제를 통해 재귀 쿼리를 활용하는 방법을 익히고, 필요에 맞게 응용해 보세요.