# CTE 与递归

URL: https://caijiao.org/postgresql/advanced/advanced-query/cte
Source: docs/postgresql/advanced/advanced-query/cte.md
Description: 介绍 PostgreSQL 中的公用表表达式 (CTE) 和递归查询，帮助开发者处理复杂的查询场景，简化查询结构和逻辑。

公用表表达式 (CTE) 是 PostgreSQL 中的一个强大特性，允许在查询中临时定义一个结果集，可以在同一个查询中多次引用。递归查询是 CTE 的一种特殊形式，用于处理分层数据结构，如树状结构和图结构。

## 公用表表达式 (CTE)

### 基本语法

CTE 使用 `WITH` 关键字来定义。它可以用于简化复杂的查询，使查询更加清晰和易于维护。

```sql
WITH cte_name AS (
  -- 子查询
  SELECT column1, column2
  FROM table_name
  WHERE condition
)
SELECT column1, column2
FROM cte_name;
````

### 示例：使用 CTE 简化查询

假设我们有一个 `employees` 表，包含员工的 ID 和经理 ID。我们想找出所有员工的姓名及其经理姓名。

```sql
WITH manager_info AS (
  SELECT id, name
  FROM employees
  WHERE position = 'Manager'
)
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
JOIN manager_info m ON e.manager_id = m.id;
```

### 多个 CTE

你可以在一个查询中定义多个 CTE，只需使用逗号分隔。

```sql
WITH cte1 AS (
  SELECT id, name
  FROM employees
  WHERE department = 'HR'
),
cte2 AS (
  SELECT id, name
  FROM employees
  WHERE department = 'Engineering'
)
SELECT cte1.name AS hr_employee, cte2.name AS engineering_employee
FROM cte1, cte2;
```

### 递归 CTE

递归 CTE 用于处理具有层级关系的数据，如组织结构、文件目录等。递归 CTE 由两个部分组成：基础部分和递归部分。

* 基础部分：返回递归查询的初始数据。
* 递归部分：通过递归调用基础部分来生成层级数据。

### 递归 CTE 基本语法

```sql
WITH RECURSIVE cte_name AS (
  -- 基础部分
  SELECT column1, column2
  FROM table_name
  WHERE condition
  UNION ALL
  -- 递归部分
  SELECT t.column1, t.column2
  FROM table_name t
  JOIN cte_name cte ON t.column1 = cte.column1
)
SELECT * FROM cte_name;
```

### 示例：递归查询

假设我们有一个 `employees` 表，存储了每个员工的 ID 和经理 ID，我们希望获取一个员工的所有直属下属及其子孙。

```sql
WITH RECURSIVE employee_hierarchy AS (
  -- 基础部分：获取指定员工的信息
  SELECT id, name, manager_id
  FROM employees
  WHERE name = 'John Doe'
  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;
```

### 递归查询应用场景

递归 CTE 在以下场景中非常有用：

* **组织结构**：查询员工及其直接和间接的上级或下属。
* **文件目录结构**：查询文件夹及其子文件夹。
* **图数据**：查询图中的节点及其相邻节点。

### 递归 CTE 优化

* **避免无限递归**：确保递归查询有结束条件，防止递归过深导致性能问题。
* **使用限制**：在递归部分中可以使用 `LIMIT` 来控制递归的层数或数量。

例如，限制递归查询只获取前 5 层数据：

```sql
WITH RECURSIVE employee_hierarchy AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE name = 'John Doe'
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
  LIMIT 5
)
SELECT * FROM employee_hierarchy;
```

## 小结

CTE 是 PostgreSQL 中非常实用的功能，能帮助开发者简化复杂查询的结构。递归 CTE 在处理分层数据时尤其重要，能够解决许多涉及树状或图状结构的问题。通过合理使用 CTE 和递归查询，能够大大提高查询的可读性、性能和可维护性。