一、PGsql 数据库递归查询

with recursive ag_tree as (select id, title, type, parent_id
                           from agreements
                           where type = 'test'
                           union all
                           select a.id, a.title, a.type, a.parent_id
                           from agreements a
                                    join ag_tree at on a.parent_id = at.id
                           where a.parent_id is not null)
select *
from ag_tree;

-- 递归查询示例：找出员工 "Alice" 的所有直接或间接下属
WITH RECURSIVE employee_tree 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_tree et ON e.manager_id = et.id
)
SELECT id, name
FROM employee_tree;

-- 分析
当进行递归查询时，我们使用 Common Table Expressions (CTE) 来实现。递归查询允许查询在查询过程中引用自身的结果。在 PostgreSQL 中，递归查询的语法包括以下几个部分：

1. **WITH RECURSIVE**：
   在递归查询的开始，我们使用 `WITH RECURSIVE` 关键字来声明一个递归查询。`WITH RECURSIVE` 告诉 PostgreSQL 这是一个递归表达式，允许查询引用自身。

2. **递归表达式**：
   在 `WITH RECURSIVE` 关键字后，我们定义一个递归表达式，给它一个名称（例如，`employee_tree`）。递归表达式中包含两个部分：初始部分（Anchor）和递归部分（Recursive）。

3. **初始部分（Anchor）**：
   初始部分是递归查询的起始点。它是一个普通的 SQL 查询，不涉及递归。它返回初始结果集，作为递归的起点。在递归表达式中，初始部分以 `SELECT` 语句开始。

   示例：`SELECT id, name, manager_id FROM employees WHERE name = 'Alice'`

   在上面的示例中，我们选择了名为 "Alice" 的员工信息，作为递归查询的起始点。

4. **递归部分（Recursive）**：
   递归部分定义了递归查询的逻辑。它引用递归表达式的结果，并根据递归条件连接到原始表或视图，以扩展结果集。

   示例：`SELECT e.id, e.name, e.manager_id FROM employees e JOIN employee_tree et ON e.manager_id = et.id`

   在上面的示例中，我们连接 `employees` 表与递归表达式 `employee_tree`，通过 `JOIN` 来找出直接下属，并将它们添加到结果集中。

5. **UNION ALL**：
   在递归表达式的递归部分中，我们使用 `UNION ALL` 运算符将初始部分（Anchor）和递归部分（Recursive）连接在一起。

   示例：`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_tree et ON e.manager_id = et.id`

   在上面的示例中，我们使用 `UNION ALL` 将初始部分和递归部分连接起来，形成递归查询。

6. **递归终止条件**：
   在递归查询中，必须有一个递归终止条件，用于结束递归。这通常在初始部分或递归部分的 `WHERE` 子句中设置，以防止无限递归。

   示例：在上面的示例中，我们并未显式设置递归终止条件。在这种情况下，递归查询会继续直到没有匹配的下属为止，自然地终止。

7. **最终 SELECT**：
   最后，我们在 `WITH RECURSIVE` 子句的末尾使用一个普通的 `SELECT` 语句来选择递归表达式的结果。这是将递归表达式结果展示给用户的最终结果集。

   示例：`SELECT id, name FROM employee_tree`

   在上面的示例中，我们选择递归表达式 `employee_tree` 中的 `id` 和 `name` 列作为最终结果。

通过 `WITH RECURSIVE` 和 `UNION ALL` 的组合，递归查询允许我们在查询过程中引用自身的结果，从而实现复杂的层次结构数据检索和处理。
-- 分析结束