中文
devexamples

Recursive CTE for an Org Chart

Walk a self-referencing employee table with a recursive CTE to list each person and their management depth from the top.

SqlData Integrity

Code

snippet
WITH RECURSIVE org_chart AS (
    SELECT
        id,
        name,
        manager_id,
        1 AS depth,
        name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.id,
        e.name,
        e.manager_id,
        oc.depth + 1,
        oc.path || ' > ' || e.name
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth, path
FROM org_chart
ORDER BY path;

Related Examples