pgsql递归与子查询的结合

PostgreSQL 支持递归查询和子查询，它们可以结合使用以满足复杂的查询需求

首先，我们创建一个表来存储员工数据：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

接下来，我们插入一些示例数据：

INSERT INTO employees (name, manager_id) VALUES ('Alice', NULL);
INSERT INTO employees (name, manager_id) VALUES ('Bob', 1);
INSERT INTO employees (name, manager_id) VALUES ('Charlie', 1);
INSERT INTO employees (name, manager_id) VALUES ('David', 2);
INSERT INTO employees (name, manager_id) VALUES ('Eve', 2);
INSERT INTO employees (name, manager_id) VALUES ('Frank', 3);

现在，我们想要查询一个员工及其所有下属。我们可以使用递归查询和子查询来实现这个目标。以下是一个示例查询，用于查询名为 “Alice” 的员工及其所有下属：

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id FROM employees WHERE name = 'Alice'
    UNION ALL
    SELECT e.id, e.name, e.manager_id FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

这个查询首先从名为 “Alice” 的员工开始，然后递归地查询其所有下属。WITH RECURSIVE 子句定义了一个递归查询，其中 employee_hierarchy 是一个临时的结果集。我们使用 UNION ALL 将基本查询（查询名为 “Alice” 的员工）与递归查询（查询下属）组合在一起。递归查询通过将当前员工的 manager_id 与上一级员工的 id 进行比较来实现。

运行此查询将返回以下结果：

 id | name   | manager_id
----+--------+------------
  1 | Alice  |
  2 | Bob    |         1
  3 | Charlie|         1
  4 | David  |         2
  5 | Eve    |         2
  6 | Frank  |         3

这样，我们就成功地结合了递归查询和子查询来查询一个员工及其所有下属。