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_idFROM employeesWHERE id = 1UNION ALL-- 2. 递归：找所有下级SELECT e.id, e.name, e.manager_idFROM employees eJOIN 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 ALLSELECT n + 1FROM numsWHERE n < 10       -- 递归终止条件
  )
  SELECT * FROM nums;

• 注意点：

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