CTE 与递归

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

公用表表达式 (CTE)

基本语法

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

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

示例：使用 CTE 简化查询

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

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，只需使用逗号分隔。

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 基本语法

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，我们希望获取一个员工的所有直属下属及其子孙。

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 层数据：

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 和递归查询，能够大大提高查询的可读性、性能和可维护性。