今天又遇到一个问题，在Oracle数据库中有Connectby这类的递归SQL。结果迁移到PostgreSQL中，发现没有level伪列。
作为去O实力派，没办法只能安排。
由于开发的表比较复杂，我们这里来造一个cats表，说明一下这个问题。我们先看看Oracle中的查询结果。
create table hbdx_zhaoyou.cats( catno number(4,0), catname varchar2(15), job varchar2(15), mgr number(4,0), constraint pk_emp primary key (catno) ); insert into hbdx_zhaoyou.cats values (0, 'king', 'ceo', null ); insert into hbdx_zhaoyou.cats values (1, 'jones', 'cio', 0); insert into hbdx_zhaoyou.cats values (2, 'blake', 'cfo', 0); insert into hbdx_zhaoyou.cats values (3, 'clark', 'hr exec', 0); insert into hbdx_zhaoyou.cats values (4, 'scott', 'it mgr', 1); insert into hbdx_zhaoyou.cats values (5, 'turner', 'architect',1); insert into hbdx_zhaoyou.cats values (6, 'adams', 'fin.mgr',2 ); insert into hbdx_zhaoyou.cats values (7, 'james', 'hr.mgr',3); insert into hbdx_zhaoyou.cats values (8, 'ford', 'it support',4); insert into hbdx_zhaoyou.cats values (9, 'miller', 'developer', 4); insert into hbdx_zhaoyou.cats values (10, 'smith', 'accountant',6); insert into hbdx_zhaoyou.cats values (11, 'allen', 'payroll clerk',6); insert into hbdx_zhaoyou.cats values (12, 'ward', 'hr officer',7 ); commit; |
我们在Oracle中执行层次查询得到下列结果。
SQL> SELECT catno,catname,job,level FROM hbdx_zhaoyou.cats CONNECT BY PRIOR catno = mgr START WITH mgr IS NULL order by level ; CATNO CATNAME JOB LEVEL ---------- --------------- --------------- ---------- 0 king ceo 1 1 jones cio 2 2 blake cfo 2 3 clark hr exec 2 6 adams fin.mgr 3 7 james hr.mgr 3 5 turner architect 3 4 scott it mgr 3 8 ford it support 4 11 allen payroll clerk 4 12 ward hr officer 4 9 miller developer 4 10 smith accountant 4 |
类似于上图，我们知道King是老大，他的下面一级是经理级别，一共有三个经理，分别是jones、blake、clark，然后以此往下推。
这里Oracle的语法：
CONNECT BY ：定义了父级别和子级别之间的关系。
PRIOR：则指定了父级
START WITH：定义我们希望查询开始的记录。
level：指示层次结构级别的伪列。
那么在PostgreSQL中有两种实现的方法，第一种叫CTE（commontable expressions），简称公用表表达式。第二种是安装自带插件，使用PG的connectby函数。
CTE(common tableexpressions)实现
CTE又叫commontable expressions，它隶属于SQL:1999标准，在Oracle11gR2版本、MySQL8.0版本、PostgreSQL9.4以上版本都支持。因为是标准的语法，我们这里用mariadb官方文档（最容易懂）的图来说明一下原理。
首选我们要使用recursive关键字来表示，这是一个递归的CTE(公用表达式)。然后第一部分叫AnchorPart，翻译过来就是锚点。这个锚点我觉得就代表了树形查询的一个展开的点，比如你要从我们CATS表的ITMGR这个节点查询，那么这个条件就是锚点。
接下来就是Recursivepart，递归的部分。这里会告诉我们每个递归的步骤将要做什么。每次执行出来的结果，就会存放到结果表中，一直到整个递归结束。
下面的图详细的说明了递归到结果集这一过程。
首选查询锚点，取出name=‘Alex’的记录放入到结果表。
通过锚点查询的数据和原来的数据表关联，查询出锚点的下一层数据。比如这里查的是Alex的father和mother。
这里将上面查出来的数据Dad和Mom存到结果表中。
接下来继续根据上面查询的Dad和Mom，继续查询他们的father和mother。
这里将上面查出来的数据GrandpaBill存到结果表中
就这样一直查，直到没有结果为止。
上面的原理和语法介绍完了，我们可以在PG中写同样的SQL实现上述Oracle中connectby功能。
with recursive cte as ( select catno, catname, mgr from cats where mgr is null union all select e.catno, e.catname, e.mgr from cte c join cats e on e.mgr = c.catno ) select * from cte; catno | catname | mgr -------+---------+----- 0 | king | 1 | jones | 0 2 | blake | 0 3 | clark | 0 4 | scott | 1 5 | turner | 1 6 | adams | 2 7 | james | 3 8 | ford | 4 9 | miller | 4 10 | smith | 6 11 | allen | 6 12 | ward | 7 |
可以看到结果类似，但是缺少像Oracle中的伪列level。这个伪列是需要我们自己构造一个的。
with recursive cte as ( select catno, catname, mgr,1 AS level from cats where mgr is null union all select e.catno, e.catname, e.mgr,c.level + 1 from cte c join cats e on e.mgr = c.catno ) select * from cte; catno | catname | mgr | level -------+---------+-----+------- 0 | king | | 1 1 | jones | 0 | 2 2 | blake | 0 | 2 3 | clark | 0 | 2 4 | scott | 1 | 3 5 | turner | 1 | 3 6 | adams | 2 | 3 7 | james | 3 | 3 8 | ford | 4 | 4 9 | miller | 4 | 4 10 | smith | 6 | 4 11 | allen | 6 | 4 12 | ward | 7 | 4 |
手动增加了一个列，就可以把level伪列功能实现了，至此我们就解决了开发的问题。
connectby实现
上面介绍了比较标准的CTE表达式，是在各种数据库都已经兼容的语法。而PG也有他自己独有的一种方法叫connectby，PG的connectby和Oracle中的connectby使用方式有很大的不同。
首先我们要安装插件tablefunc，这个插件是软件自带的。安装很简单直接createextension tablefunc就可以了。
这个插件有很多功能，我们这里只用最后一个功能connectby。
connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ]) |
relname | 源表的名称 |
keyid_fld | 关键字段 |
parent_keyid_fld | 父键的关键字段 |
orderby_fld | 排序同级别字段（可选） |
start_with | 起始行的键值 |
max_depth | 要向下展开的最大深度，零表示无限深度 |
branch_delim | 在分支输出中用于分隔键值的字符串（可 选 |
接下来就是见证这个函数魅力的时候了。
SELECT * FROM connectby('cats', 'catno', 'mgr', '0', 0, '->') AS t(keyid numeric, parent_keyid numeric, level int, branch text) order by level asc; keyid | parent_keyid | level | branch -------+--------------+-------+------------- 0 | | 0 | 0 1 | 0 | 1 | 0->1 2 | 0 | 1 | 0->2 3 | 0 | 1 | 0->3 4 | 1 | 2 | 0->1->4 6 | 2 | 2 | 0->2->6 7 | 3 | 2 | 0->3->7 5 | 1 | 2 | 0->1->5 9 | 4 | 3 | 0->1->4->9 8 | 4 | 3 | 0->1->4->8 10 | 6 | 3 | 0->2->6->10 11 | 6 | 3 | 0->2->6->11 12 | 7 | 3 | 0->3->7->12 |
可以看到使用起来非常方便，直接就输入参数就行了。当然这里有一个问题是我们没办法通过connectby函数展示全部的列，它的函数参数写死了类型。如果我们要展示cats表中的catname，就需要我们把当前结果集和原表cats在做一次关联。
select keyid,catname,parent_keyid,level+1 from connectby('cats', 'catno', 'mgr', '0', 0, '->') AS t(keyid numeric, parent_keyid numeric, level int, branch text) inner join cats on catno = keyid order by level,mgr asc; keyid | catname | parent_keyid | ?column? -------+---------+--------------+---------- 0 | king | | 1 1 | jones | 0 | 2 2 | blake | 0 | 2 3 | clark | 0 | 2 5 | turner | 1 | 3 4 | scott | 1 | 3 6 | adams | 2 | 3 7 | james | 3 | 3 9 | miller | 4 | 4 8 | ford | 4 | 4 10 | smith | 6 | 4 11 | allen | 6 | 4 12 | ward | 7 | 4 |
那么这么写就和我前面的withrecursive的结果集完全一致了。
谁的效率高？
如果拿我们想要的结果来看的话。通过执行计划来看，很明显是CTE快一些。
就算我们不取catname，取消掉关联，connecyby函数执行的效率也是比CTE低的。
终于有人喊出了口号：
CONNECTBY Is Dead,
LongLive CTE!
参考文档：
RecursiveCommon Table Expressions Overview
https://mariadb.com/kb/en/recursive-common-table-expressions-overview/
Hierarchicaland recursive queries in SQL
https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression
F.38.tablefunc
https://www.postgresql.org/docs/12/tablefunc.html
