[SQL] 연습 문제 모음5

🔒 문제

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    manager_id INT,  -- 상사의 employee_id (NULL이면 최고 관리자)
    department VARCHAR(50)  -- 부서
);

-- 직원 데이터 삽입
INSERT INTO employees (employee_id, first_name, last_name, hire_date, manager_id, department) VALUES
(1, 'John', 'Doe', '2020-01-01', NULL, 'HR'),      -- 최고 관리자 (상사가 없음)
(2, 'Jane', 'Smith', '2021-03-15', 1, 'HR'),       -- John의 부하
(3, 'Emily', 'Wilson', '2022-02-01', 1, 'Finance'), -- John의 부하
(4, 'Michael', 'Johnson', '2021-06-01', 2, 'HR'),   -- Jane의 부하
(5, 'Chris', 'Lee', '2022-07-01', 2, 'Finance'),   -- Jane의 부하
(6, 'Sarah', 'Brown', '2023-01-01', 3, 'Finance'), -- Emily의 부하
(7, 'Tom', 'Hanks', '2023-02-01', 3, 'Finance'),   -- Emily의 부하
(8, 'Linda', 'Davis', '2022-05-10', 4, 'HR');   

특정 부서에 속한 직원들의 상사-부하 관계를 추적하고, 각 직원의 경로를 최고 관리자부터 해당 직원까지 추적합니다.
경로 추적에 대해 최대 3단계까지만 경로를 출력하시오.
예시: HR 부서의 모든 직원에 대해, 최고 관리자부터 해당 직원까지의 경로를 찾으세요.

 

🔓 내가 푼 답

WITH RECURSIVE hierarchy AS (

-- 초기값, 최고 권위자 행

SELECT employee_id, first_name, last_name, hire_date, manager_id, department, 1 AS level

FROM employees

WHERE department = 'HR' AND manager_id IS NULL -- 최고 관리자

 

UNION ALL

 

-- 반복 : 부하직원 구하기

SELECT e.employee_id, e.first_name, e.last_name, e.hire_date, e.manager_id, e.department, h.level + 1

FROM employees e

JOIN hierarchy h ON e.manager_id = h.employee_id

WHERE e.department = 'HR' AND h.level < 3 -- 경로 깊이가 3단계를 초과하지 않도록 제한

)

select group_concat(employee_id order by level,employee_id separator ' > ')

from hierarchy;

<결과>

✅ 사용 문법 정리

재귀 쿼리 WITH RESURSIVE~

# 1부터 5까지 출력
WITH RECURSIVE numbers AS (
    -- 초기값: 1
    SELECT 1 AS num

    UNION ALL

    -- 재귀적으로 숫자 증가
    SELECT num + 1
    FROM numbers
    WHERE num < 5
)
SELECT num FROM numbers;

 

GROUP_CONCAT(expression [ORDER BY expression] [SEPARATOR separator])

: 여러 행을 하나의 문자열로 결합하는 기능