简化 SQL 递归查询
背景描述
自引用类型的表结构处理起来比较麻烦，比如“分类”表，通常包括自己的ID和父分类ID，当我们要做父分类路径、子分类路径之类的查询时很不方便，例如我们会使用嵌套查询，或者添加冗余字段来记录分类路径信息，都比较麻烦，有没有简单的办法呢？
解决方法就是使用CTE（Common Table Expression），通用表表达式。
下面我们先认识一下CTE，然后通过几个实际查询示例来深入理解，最后会提供测试数据，以方便自己动手实践（在mysql8和postgres10上都测试过）。
什么是 CTE？
各大主流数据库都支持 CTE，mysql8 中也支持了。
简单理解，CTE 就是一个有名字的结果集，就像一个普通表一样，可以被用在 select 语句中。
CTE 有循环和非循环形式，非循环形式比较简单，就像一个命了名的子查询，例如：
WITH one AS (
SELECT 1 AS number_one
),
two AS (
SELECT 2 AS number_two
)
SELECT * FROM one, two;这里定义了2个CTE：
- one，有一个列 numberone_，值为 1。
- two，有一个列 number_two，值为 2。
执行结果：
number_one | number_two
------------+------------
1 | 2
(1 row)循环形式的复杂一点，先看一个示例：
WITH RECURSIVE counter AS (
SELECT 1 as n
UNION ALL
SELECT n + 1 FROM counter WHERE n < 10
)
SELECT * from counter;
# 执行结果
n
----
1
2
3
4
5
6
7
8
9
10
(10 rows)循环式CTE的执行思路：
第一个 select 会产生N个种子记录，添加到结果集，然后执行后面的 select，这个 select 会基于前面 select 产生的结果集运行，把执行结果添加到结果集，接下来会继续执行这个 select，还是基于上一个 select 产生的数据，并把执行结果添加到结果集，一直到执行结果为空，结束。2个 select 的联结词包括 UNION ALL 和 UNION，区别就是 UNION 会把重复的结果删掉。
结合上面的例子：
- 第一个 select 产生一条数据，列名为 "n"，值为 “1”，放入结果集。
- 第二个 select 在这条数据的基础上执行，符合 n<10 这个条件，执行 n+1，产生一条结果数据 “2”，放入结果集。
- 第二个 select 继续执行，基于上次执行结果 “2” 执行，符合 n<10 这个条件，执行 n+1，产生一条结果数据 “3”，放入结果集。
- ...
- 一直执行到不符合 n<10 这个条件，执行结果集为空，结束。
示例
示例用的的数据：
表 categories
id | name | parent_id |
|---|---|---|
1 | Root A | |
2 | Root B | |
3 | Child A1 | 1 |
4 | Child A2 | 1 |
5 | Child B1 | 2 |
6 | Child B2 | 2 |
7 | Grandchild A1a | 3 |
8 | Grandchild A1b | 3 |
层级结构是这样的：
Root A --> Child A1 --> Grandchild A1a
| \-> Grandchild A1b
\-> Child A2
Root B --> Child B1
\-> Child B2（1）示例1
查询 “Child A1” 这个分类及其子分类，并显示层级深度。
WITH RECURSIVE sub_tree AS (
SELECT id, name, 1 AS relative_depth
FROM categories
WHERE name = 'Child A1'
UNION ALL
SELECT cat.id, cat.name, st.relative_depth + 1
FROM categories cat, sub_tree st
WHERE cat.parent_id = st.id
)
SELECT * FROM sub_tree;执行结果：
id | name | relative_depth
----+----------------+----------------
3 | Child A1 | 1
7 | Grandchild A1a | 2
8 | Grandchild A1b | 2
(3 rows)分析：
- 第一个select得到结果数据
3,ChildA1,1。 - 第二个select把 categories 表和第一个select的结果集进行关联，得到2条数据，
7,GrandchildA1a和8,GrandchildA1b，这2条数据都会在3,ChildA1,1基础上计算relative_depth+1，所以结果都为 "2"。 - 第二个 select 继续执行，发现结果集为空了，停止。
（2）示例2
查询 "Grandchild A1b" 的所有父分类。
WITH RECURSIVE parents AS (
SELECT id, name, parent_id, 0 AS relative_depth
FROM categories
WHERE name = 'Grandchild A1b'
UNION ALL
SELECT cat.id, cat.name, cat.parent_id, p.relative_depth - 1
FROM categories cat, parents p
WHERE cat.id = p.parent_id
)
SELECT id, name, relative_depth FROM parents;执行结果：
id | name | relative_depth
----+----------------+----------------
8 | Grandchild A1b | 0
3 | Child A1 | -1
1 | Root A | -2
(3 rows)执行思路与示例1相同。
（3）示例3
查询根分类及其所有子分类。
WITH RECURSIVE categories_with_roots AS (
SELECT id, parent_id, name, name as root_name
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT cat.id, cat.parent_id, cat.name, cwr.root_name
FROM categories cat, categories_with_roots cwr
WHERE cat.parent_id = cwr.id
)
SELECT name, root_name FROM categories_with_roots;执行结果：
name | root_name
----------------+-----------
Root A | Root A
Root B | Root B
Child A1 | Root A
Child A2 | Root A
Child B1 | Root B
Child B2 | Root B
Grandchild A1a | Root A
Grandchild A1b | Root A分析：
- 第一个 select 得到2条记录
RootA和RootB。 - 第二个 select 把 categories 表和第一个 select 的结果集进行联合，找到了
RootA的2个子分类ChildA1和ChildA2，还有RootB的2个子分类ChildB1和ChildB2。 - 第二个 select 继续执行，把 categories 和上次执行结果联合，基于
ChildA1、ChildA2、ChildB1、ChildB2查找，找到了GrandchildA1a和GrandchildA1b。 - 第二个 select 继续执行，发现结果集为空了，停止。
测试环境准备
建表：
CREATE TABLE test.categories
(
id int,
name varchar(50),
parent_id int
);插入测试数据：
INSERT INTO categories (id, name) VALUES (1,'Root A');
INSERT INTO categories (id, name) VALUES (2,'Root B');
INSERT INTO categories (id, name, parent_id) VALUES (3,'Child A1',1);
INSERT INTO categories (id, name, parent_id) VALUES (4,'Child A2',1);
INSERT INTO categories (id, name, parent_id) VALUES (5,'Child B1',2);
INSERT INTO categories (id, name, parent_id) VALUES (6,'Child B2',2);
INSERT INTO categories (id, name, parent_id) VALUES (7,'Grandchild A1a',3);
INSERT INTO categories (id, name, parent_id) VALUES (8,'Grandchild A1b',3);