Postgresql 递归自连接

# 使用 PostgreSQL 进行递归自连接

sql
-- 创建一个示例表格，表示员工与经理的关系
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manager_id INT REFERENCES employees(id)
);
-- 插入一些示例数据
INSERT INTO employees (name, manager_id) VALUES
    ('Alice', NULL),
    ('Bob', 1),
    ('Charlie', 2),
    ('David', 1),
    ('Eva', 3),
    ('Frank', 3);
-- 使用递归自连接查询员工及其直接和间接经理
WITH RECURSIVE manager_hierarchy AS (
    SELECT
        id,
        name,
        manager_id
    FROM
        employees
    WHERE
        manager_id IS NULL
    UNION
    SELECT
        e.id,
        e.name,
        e.manager_id
    FROM
        employees e
    INNER JOIN
        manager_hierarchy m ON e.manager_id = m.id
)
SELECT * FROM manager_hierarchy;