自引用类型的表结构处理起来比较麻烦,比如“分类”表,通常包括自己的ID和父分类ID,当我们要做父分类路径、子分类路径之类的查询时很不方便,例如我们会使用嵌套查询,或者添加冗余字段来记录分类路径信息,都比较麻烦,有没有简单的办法呢?
解决方法就是使用CTE(Common Table Expression),通用表表达式。
下面我们先认识一下CTE,然后通过几个实际查询示例来深入理解,最后会提供测试数据,以方便自己动手实践(在mysql8和postgres10上都测试过)。
各大主流数据库都支持 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:
执行结果:
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 会把重复的结果删掉。
结合上面的例子:
示例用的的数据:
表 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
查询 “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)
分析:
3,ChildA1,1
。7,GrandchildA1a
和 8,GrandchildA1b
,这2条数据都会在 3,ChildA1,1
基础上计算 relative_depth
+1
,所以结果都为 "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相同。
查询根分类及其所有子分类。
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
分析:
RootA
和 RootB
。RootA
的2个子分类 ChildA1
和 ChildA2
,还有 RootB
的2个子分类 ChildB1
和 ChildB2
。ChildA1、ChildA2、ChildB1、ChildB2
查找,找到了 GrandchildA1a
和 GrandchildA1b
。建表:
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);