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

這樣，我們就成功地結合了遞歸查詢和子查詢來查詢一個員工及其所有下屬。