SQL学习：WITH RECURSIVE

SQL学习：WITH RECURSIVE

• 什么是WITH RECURSIVE：

  • WITH RECURSIVE（递归 CTE）用于在 SQL 中写递归查询，常见场景如层级结构（组织、分类树）、序列生成等。

• 基本语法结构：

  WITH RECURSIVE cte_name AS (
      -- 1. 基础查询（初始层）
      SELECT ...
      FROM ...
      WHERE ...   -- 起点条件
  
      UNION ALL
  
      -- 2. 递归部分（下一层如何生成）
      SELECT ...
      FROM ...
      JOIN cte_name ON ...   -- 递归条件
  )
  SELECT * FROM cte_name;
  
  

  递归 CTE 一定包含两部分：

  • Anchor（锚点）：第一次取什么数据；

  • Recursive（递归）：如何从上一层继续往下走；

• 例子1：查询所有子级（树型数据）

  假设有一张员工表：
  
  employees
  ---------
  id
  name
  manager_id  -- 上级 ID
  

• 查询某人（例如 id = 1）的所有下属：

  WITH RECURSIVE all_subs AS (
      -- 1. 锚点：从这个员工开始
      SELECT id, name, manager_id
      FROM employees
      WHERE id = 1
  
      UNION ALL
  
      -- 2. 递归：找所有下级
      SELECT e.id, e.name, e.manager_id
      FROM employees e
      JOIN all_subs s ON e.manager_id = s.id
  )
  SELECT * FROM all_subs;
  
  

• 例子2：生成从 1 到 10 的数字序列

  WITH RECURSIVE nums AS (
      SELECT 1 AS n      -- 起点
      UNION ALL
      SELECT n + 1
      FROM nums
      WHERE n < 10       -- 递归终止条件
  )
  SELECT * FROM nums;
  
  

• 注意点：

  • 注意点	说明
    必须有停止条件	否则会无限循环
    UNION ALL > UNION	常用 UNION ALL，更快也允许重复
    需要数据库支持	MySQL 8.0+、PostgreSQL、SQLite、MariaDB 10.2+等