Sql学习第三天——SQL 关于CTE(公用表达式)的递归查询使用

让我来详细讲解一下关于 SQL 中 CTE（公用表达式）的递归查询使用。

什么是 CTE

CTE，全称 Common Table Expression，是用于创建可被其他查询引用的临时结果集的方法，它是一种类似于子查询的结构，但可以被选择、更新、删除或者插入等其他查询复用。

递归查询

递归查询是指在一张表当中进行自我引用的查询操作，用于在具有父子关系的数据中查询某个节点的所有父节点或者所有子节点。

CTE 的递归查询经常应用于树形结构的数据查询，如分类目录、公司组织架构等。

使用 CTE 进行递归查询

CTE 的递归查询需要使用 WITH RECURSIVE 子句进行声明，其基本语法如下：

WITH RECURSIVE [cte_name] ([column1,column2,...]) AS (
  -- non-recursive term
  SELECT [column1,column2,...] FROM [table_name] WHERE [condition]
  UNION ALL
  -- recursive term
  SELECT [column1,column2,...] FROM [table_name] JOIN [cte_name] ON [cte_name].[column_name] = [table_name].[column_name]
  WHERE [condition]
)
SELECT [column1,column2,...] FROM [cte_name];

其中，WITH 子句后的 RECURSIVE 关键字指明了此处使用的是递归查询方式。[cte_name] 表示这个 CTE 的名称，可以取任意合法的标识符。[column1,column2,...] 表示列名称，可以自己指定或从数据表中选取。注意，递归查询必须包含一个非递归查询部分和一个递归查询部分，它们之间的联合是通过 UNION ALL 连接的。

下面通过两个示例来说明 CTE 的递归查询的用法。

示例一

首先，创建一个测试表格employee来表示公司组织架构，它包含三列数据分别是id、name和manager_id。

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name varchar(100) NOT NULL,
  manager_id INT
);

INSERT INTO employee (id, name, manager_id) VALUES
(1, 'sam', NULL),
(2, 'mary', 1),
(3, 'jim', 1),
(4, 'andy', 2),
(5, 'bob', 2),
(6, 'lucy', 3),
(7, 'test', 2),
(8, 'mitch', 6);

接下来，使用 CTE 对该表进行递归查询，以查找每一个雇员直接或者间接汇报给的经理。

WITH RECURSIVE emp_list(id, name, manager_id, level) AS (
  -- non-recursive term
  SELECT id, name, manager_id, 0 FROM employee WHERE id = 1
  UNION ALL
  -- recursive term
  SELECT e.id, e.name, e.manager_id, el.level + 1
  FROM employee e
  JOIN emp_list el ON e.id = el.manager_id
)
SELECT id, name, manager_id, level FROM emp_list;

以上 SQL 语句中，第一项非递归查询查询了指定 id 的经理信息，第二项递归查询查找添加到已查询经理列表中的直接下属，通过 JOIN 条件限制条件，一直递归到这个经理没有直接下属为止。

输出结果为：

| id |  name | manager_id | level |
|----|-------|------------|-------|
|  1 |   sam |       NULL |     0 |
|  2 |  mary |          1 |     1 |
|  3 |   jim |          1 |     1 |
|  7 |  test |          2 |     2 |
|  4 |  andy |          2 |     2 |
|  5 |   bob |          2 |     2 |
|  6 |  lucy |          3 |     2 |
|  8 | mitch |          6 |     3 |

输出结果中包括了 id，name 和 manager_id 以及每个雇员距离经理的层数 level，其中经理的 level 为 0。

示例二

接下来，使用 CTE 对一张包含公司部门分类信息的表格department进行递归查询，以查找公司部门的整个分类结构。

CREATE TABLE department (
  id INT PRIMARY KEY,
  name varchar(100) NOT NULL,
  parent_id INT
);

INSERT INTO department (id, name, parent_id) VALUES
(1, 'Animal', NULL),
(2, 'Mammal', 1),
(3, 'Bird', 1),
(4, 'Dog', 2),
(5, 'Cat', 2),
(6, 'Parrot', 3),
(7, 'Pea chicken', 3),
(8, 'Golden retriever', 4);

WITH RECURSIVE department_tree(id, name, parent_id, level) AS (
  SELECT id, name, parent_id, 0 FROM department WHERE id = 1
  UNION ALL
  SELECT dt.id, dt.name, dt.parent_id, dtl.level + 1
  FROM department dt
  JOIN department_tree dtl ON dt.parent_id = dtl.id
)
SELECT id, name, parent_id, level FROM department_tree;

以上 SQL 语句中，第一项非递归查询查询了指定 id 的部门信息，第二项递归查询查找添加到已查询部门列表中的直接上级，通过 JOIN 条件限制条件，一直递归到这个部门没有直接上级为止。

输出结果为：

| id |         name        | parent_id | level |
|----|---------------------|-----------|-------|
|  1 | Animal              |      NULL |     0 |
|  2 | Mammal              |         1 |     1 |
|  5 | Cat                 |         2 |     2 |
|  4 | Dog                 |         2 |     2 |
|  8 | Golden retriever    |         4 |     3 |
|  3 | Bird                |         1 |     1 |
|  6 | Parrot              |         3 |     2 |
|  7 | Pea chicken         |         3 |     2 |

输出结果中包括了 id，name 和 parent_id 以及每个部门距离根节点的层数 level，其中根节点的 level 为 0。

这就是关于 CTE 的递归查询的完整攻略。通过使用 CTE 实现递归查询，可以更加高效地查询具有层级关系的数据，并且在查询效率和查询结果的可读性方面都具有优势。