简化 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:

  1. one,有一个列 numberone_,值为 1。
  2. 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 ALLUNION,区别就是 UNION 会把重复的结果删掉。

结合上面的例子:

  1. 第一个 select 产生一条数据,列名为 "n",值为 “1”,放入结果集。
  2. 第二个 select 在这条数据的基础上执行,符合 n<10 这个条件,执行 n+1,产生一条结果数据 “2”,放入结果集。
  3. 第二个 select 继续执行,基于上次执行结果 “2” 执行,符合 n<10 这个条件,执行 n+1,产生一条结果数据 “3”,放入结果集。
  4. ...
  5. 一直执行到不符合 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)

分析:

  1. 第一个select得到结果数据 3,ChildA1,1
  2. 第二个select把 categories 表和第一个select的结果集进行关联,得到2条数据, 7,GrandchildA1a8,GrandchildA1b,这2条数据都会在 3,ChildA1,1基础上计算 relative_depth +1,所以结果都为 "2"。
  3. 第二个 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

分析:

  1. 第一个 select 得到2条记录 RootARootB
  2. 第二个 select 把 categories 表和第一个 select 的结果集进行联合,找到了 RootA 的2个子分类 ChildA1ChildA2,还有 RootB 的2个子分类 ChildB1ChildB2
  3. 第二个 select 继续执行,把 categories 和上次执行结果联合,基于 ChildA1、ChildA2、ChildB1、ChildB2 查找,找到了 GrandchildA1aGrandchildA1b
  4. 第二个 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);

本文分享自微信公众号 - 性能与架构(yogoup)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-09-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏C/C++基础

MySQL对CREATE TABLE IF NOT EXISTS SELECT的处理

MySQL支持创建持数据表时判断是否存在,存在则不创建,不存在则创建,相应语句如下:

17930
来自专栏lgp20151222

mysql explain用法和结果的含义

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

21910
来自专栏james大数据架构

你真的会玩SQL吗?删除重复数据且只保留一条

在网上看过一些解决方法 我在此给出的方法适用于无唯一ID的情形 表:TB_MACVideoAndPicture 字段只有2个:mac,content mac作为...

20580
来自专栏电光石火

SQL 性能调优

我们要做到不但会写SQL,还要做到写出性能优良的SQL,以下为笔者学习、摘录、并汇总部分资料与大家分享! (1)选择最有效率的表名顺序(只在基于规则的优...

34760
来自专栏数据库

Java SQL语句优化经验

. (1) 选择最有效率的表名顺序(只在基于规则的seo/' target='_blank'>优化器中有效): ORACLE 的解析器按照从右到左的顺序处理FR...

42590
来自专栏lgp20151222

索引之单列索引和组合索引

若有组合索引(a,b,c),那么根据最左前缀,数据库成立了三个索引(a)(a,b)(a,b,c),

16030
来自专栏CodingToDie

MySQL查询优化

1. 查询优化 1. 查询优化 1.2.1. 使用关联查询优化 1.2.2. 使用范围查询 1.1. 最大值和最小值的优化 1.2. 优化 Limit 分页 ...

38960
来自专栏面朝大海春暖花开

mysql树形结构递归查询

之前一直用的是Oracle,对于树形查询可以使用start with ... connect by 

1.5K40
来自专栏个人随笔

sql sever分组查询和连接查询

分组查询 select 查询信息 from 表名 where 条件 group by 按照列分组(可多个 ,隔开) order by 排序方式 (查询信息如果列...

39650
来自专栏乐沙弥的世界

SQL 基础-->创建和管理表

(列名 数据类型 [ default 默认值] [ 约束条件] [ , ......] )

6310

扫码关注云+社区

领取腾讯云代金券