简化 SQL 递归查询

2018-10-12   出处:性能与架构  作/译者:杜亦舒  

背景描述

自引用类型的表结构处理起来比较麻烦,比如“分类”表,通常包括自己的ID和父分类ID,当我们要做父分类路径、子分类路径之类的查询时很不方便,例如我们会使用嵌套查询,或者添加冗余字段来记录分类路径信息,都比较麻烦,有没有简单的办法呢?

解决方法就是使用CTE(Common Table Expression),通用表表达式。

下面我们先认识一下CTE,然后通过几个实际查询示例来深入理解,最后会提供测试数据,以方便自己动手实践(在mysql8和postgres10上都测试过)。

什么是 CTE?

各大主流数据库都支持 CTE,mysql8 中也支持了。

简单理解,CTE 就是一个有名字的结果集,就像一个普通表一样,可以被用在 select 语句中。

CTE 有循环非循环形式,非循环形式比较简单,就像一个命了名的子查询,例如:

这里定义了2个CTE:

  1. one,有一个列 numberone_,值为 1。

  2. two,有一个列 number_two,值为 2。

执行结果:

循环形式的复杂一点,先看一个示例:

循环式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

层级结构是这样的:

(1)示例1

查询 “Child A1” 这个分类及其子分类,并显示层级深度。

执行结果:

分析:

  1. 第一个select得到结果数据 3,ChildA1,1

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

  3. 第二个 select 继续执行,发现结果集为空了,停止。

(2)示例2

查询 "Grandchild A1b" 的所有父分类。

执行结果:


执行思路与示例1相同。

(3)示例3

查询根分类及其所有子分类。

执行结果:

分析:

  1. 第一个 select 得到2条记录 RootA  RootB

  2. 第二个 select 把 categories 表和第一个 select 的结果集进行联合,找到了 RootA 的2个子分类 ChildA1  ChildA2,还有 RootB 的2个子分类 ChildB1  ChildB2

  3. 第二个 select 继续执行,把 categories 和上次执行结果联合,基于 ChildA1ChildA2ChildB1ChildB2 查找,找到了 GrandchildA1a  GrandchildA1b

  4. 第二个 select 继续执行,发现结果集为空了,停止。

测试环境准备

建表:

插入测试数据:


欢迎给测试窝投稿或参与内容翻译工作,请邮件至editors@testwo.com。也欢迎大家通过新浪微博(@测试窝)或微信公众号(测试窝)关注我们,并与我们的编辑和其他窝友交流。
128°|1285 人阅读|0 条评论

登录 后发表评论
最新文章