SQL 递归查询

1. 什么是递归查询

递归查询是指在数据库中建立一种自引用的关系，通过反复迭代这种关系，从而对数据进行深度遍历和查询的过程。递归查询通常使用的场景是在具有层级结构的数据中，例如组织结构、树形结构、图形结构等。通过递归查询，我们可以方便地获取和处理这种结构化数据。

在 SQL 中实现递归查询，主要是通过使用 WITH RECURSIVE 关键字来定义递归查询的语法。下面将详细介绍如何使用递归查询来处理各种场景。

2. 递归查询的语法

在使用递归查询之前，需要了解一下递归查询的基本语法：

WITH RECURSIVE <递归查询名称> (column1, column2, ...) AS (
    <初始查询语句>
    UNION ALL
    <递归查询语句>
)
SELECT * FROM <递归查询名称>;

在这个语法中，关键字 WITH RECURSIVE 用来指定递归查询。<递归查询名称> 是指定的递归查询的名称，可以自定义。

<初始查询语句> 用来获取初始数据集合，这个语句的结果集会被用作递归查询的初始值。可以是一个普通的查询语句，也可以是一个子查询。

UNION ALL 是用来连接初始查询和递归查询的操作符，它会将两个查询语句的结果集合并在一起。

<递归查询语句> 是指递归查询的定义语句，它用来从前一次递归查询的结果中继续查询下一层级的数据。

在递归查询语句中，可以使用之前定义的递归查询名称来引用上一次的结果集，以实现迭代查询。

最后的 SELECT 语句用来从递归查询名称所指定的结果集中选择查询的字段。

3. 使用递归查询实现组织结构的遍历

假设我们有一个包含部门组织结构的表格 departments，其中包含了部门的 ID、名称和上级部门的 ID。我们希望通过递归查询获取到整个组织结构，并按照层级关系进行展示。

首先，我们需要先创建这个表格，并插入一些示例数据：

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  parent_id INT
);

INSERT INTO departments (id, name, parent_id) VALUES
  (1, '总部', NULL),
  (2, '研发部', 1),
  (3, '市场部', 1),
  (4, '销售部', 1),
  (5, '前端开发组', 2),
  (6, '后端开发组', 2),
  (7, '市场推广组', 3),
  (8, '客户拓展组', 4);

现在，我们可以使用递归查询来遍历整个组织结构：

WITH RECURSIVE org_structure (id, name, parent_id, level) AS (
  SELECT id, name, parent_id, 0
  FROM departments
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, os.level + 1
  FROM org_structure os
  JOIN departments d
    ON os.id = d.parent_id
)
SELECT id, name, parent_id, level
FROM org_structure;

运行上面的查询语句，可以得到以下结果：

 id |     name      | parent_id | level
----+---------------+-----------+-------
  1 | 总部          |           |     0
  2 | 研发部        |         1 |     1
  3 | 市场部        |         1 |     1
  4 | 销售部        |         1 |     1
  5 | 前端开发组    |         2 |     2
  6 | 后端开发组    |         2 |     2
  7 | 市场推广组    |         3 |     2
  8 | 客户拓展组    |         4 |     2

通过递归查询，我们成功地获取到了整个组织结构的层级关系。

4. 使用递归查询实现获取树形结构的路径

另一个常见的使用递归查询的场景是获取树形结构中某个节点的所有祖先节点或路径。

假设我们有一个包含地理区域的表格 regions，其中包含了区域的 ID、名称和上级区域的 ID。我们希望通过递归查询，获取到某个区域节点的完整路径。

首先，我们需要先创建这个表格，并插入一些示例数据：

CREATE TABLE regions (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  parent_id INT
);

INSERT INTO regions (id, name, parent_id) VALUES
  (1, '中国', NULL),
  (2, '北京', 1),
  (3, '上海', 1),
  (4, '广东', 1),
  (5, '朝阳区', 2),
  (6, '海淀区', 2),
  (7, '浦东新区', 3),
  (8, '番禺区', 4);

现在，我们可以使用递归查询来获取指定区域的完整路径：

WITH RECURSIVE region_path (id, name, parent_id, path) AS (
  SELECT id, name, parent_id, name::text
  FROM regions
  WHERE id = 7
  UNION ALL
  SELECT r.id, r.name, r.parent_id, rp.path || ' -> ' || r.name::text
  FROM region_path rp
  JOIN regions r
    ON rp.parent_id = r.id
)
SELECT path
FROM region_path;

运行上面的查询语句，可以得到以下结果：

            path
------------------------------
 中国 -> 上海 -> 浦东新区

通过递归查询，我们成功地获取到了指定区域的完整路径。

5. 使用递归查询实现查询层级关系的数据

在一些场景中，我们可能需要查询包含层级关系的数据，并在结果中以层级的形式展示。递归查询可以帮助我们方便地获取到这样的结果。

假设我们有一个包含层级关系的表格 categories，其中包含了分类的 ID、名称和上级分类的 ID。我们希望通过递归查询，获取到所有分类的层级关系，并按照层级进行展示。

首先，我们需要先创建这个表格，并插入一些示例数据：

CREATE TABLE categories (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  parent_id INT
);

INSERT INTO categories (id, name, parent_id) VALUES
  (1, '电子产品', NULL),
  (2, '手机', 1),
  (3, '电视', 1),
  (4, '数码相机', 1),
  (5, 'iPhone', 2),
  (6, '小米', 2),
  (7, '三星', 2),
  (8, '索尼', 3),
  (9, '海信', 3),
  (10, '尼康', 4),
  (11, '佳能', 4);

现在，我们可以使用递归查询来获取所有分类的层级关系：

WITH RECURSIVE category_hierarchy (id, name, parent_id, level) AS (
  SELECT id, name, parent_id, 0
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ch.level + 1
  FROM category_hierarchy ch
  JOIN categories c
    ON ch.id = c.parent_id
)
SELECT id, name, parent_id, level
FROM category_hierarchy
ORDER BY level, id;

运行上面的查询语句，可以得到以下结果：

 id |     name     | parent_id | level
----+--------------+-----------+-------
  1 | 电子产品     |           |     0
  2 | 手机         |         1 |     1
  3 | 电视         |         1 |     1
  4 | 数码相机     |         1 |     1
  5 | iPhone      |         2 |     2
  6 | 小米         |         2 |     2
  7 | 三星         |         2 |     2
  8 | 索尼         |         3 |     2
  9 | 海信         |         3 |     2
 10 | 尼康         |         4 |     2
 11 | 佳能         |         4 |     2

通过递归查询，我们成功地获取到了所有分类的层级关系，并按照层级和 ID 进行了排序。