WITH RECURSIVE递归（4个例子）

在SQL中，WITH RECURSIVE可以用来创建递归查询，这种查询会根据给定的条件递归地调用自身。递归查询通常用于处理层次化或递归关系的数据，例如目录结构、组织架构或是计算复利投资值。

以下是使用WITH RECURSIVE进行递归查询的四个例子：

1. 计算阶乘:

WITH RECURSIVE factorial(n, fact) AS (
  SELECT 1, 1
  UNION ALL
  SELECT n + 1, n * fact FROM factorial WHERE n < 10
)
SELECT n, fact FROM factorial ORDER BY n;

2. 列出所有员工及其直接下属:

WITH RECURSIVE employees(id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, level + 1
  FROM employees e
  INNER JOIN employees m ON e.manager_id = m.id
)
SELECT * FROM employees;

3. 计算公司每个员工的直接上级:

WITH RECURSIVE managers(id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, level + 1
  FROM employees e
  INNER JOIN managers m ON e.manager_id = m.id
)
SELECT * FROM managers;

4. 计算文件夹大小（递归计算子文件夹内文件的大小）:

WITH RECURSIVE folder_size(folder_id, size) AS (
  SELECT folder_id, SUM(file.size) AS size
  FROM files file
  WHERE NOT EXISTS (SELECT 1 FROM folders subfolder WHERE subfolder.parent_id = file.folder_id)
  GROUP BY folder_id
  UNION ALL
  SELECT subfolder.id, folder_size.size + SUM(files.size)
  FROM folders subfolder
  JOIN folder_size ON folder_size.folder_id = subfolder.parent_id
  LEFT JOIN files ON subfolder.id = files.folder_id
  GROUP BY subfolder.id, folder_size.size
)
SELECT * FROM folder_size;

这些例子展示了递归查询的不同应用场景，从计算简单的阶乘到处理层级化的数据结构。递归查询在处理层次化或递归关系的数据时非常有用。