概述
我们平时在处理不确定深度的层级结构，比如组织机构，一个常用的设计是在一张表里面保存 ID 和 Parent_ID ，并且通过自联结的办法构造一颗树。这种方式对写数据的过程很友好，但是查询过程就变得相对复杂。在不引入MPTT模型的前提下，必须通过递归算法来查询某个节点和下级子节点。
Oracle提供的connect by扩展语法，简单好用。那么，PG又是怎么实现呢？
一、CTE
1、with语句
WITH语句通常被称为通用表表达式（Common Table Expressions）或者CTEs。
WITH语句作为一个辅助语句依附于主语句，WITH语句和主语句都可以是SELECT，INSERT，UPDATE，DELETE中的任何一种语句。
WITH语句最基本的功能是把复杂查询语句拆分成多个简单的部分，如下例所示
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales
)
SELECT
region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;该例中，定义了两个WITH辅助语句，regional_sales和top_regions。前者算出每个区域的总销售量，后者了查出所有销售量占所有地区总销售里10%以上的区域。主语句通过将这个CTEs及订单表关联，算出了顶级区域每件商品的销售量和销售额。
2、WITH RECURSIVE
WITH语句还可以通过增加RECURSIVE修饰符来引入它自己，从而实现递归
WITH RECURSIVE一般用于处理逻辑上层次化或树状结构的数据，典型的使用场景是寻找直接及间接子结点。
严格来讲，这个过程实现上是一个迭代的过程而非递归，不过RECURSIVE这个关键词是SQL标准委员会定立的，所以PostgreSQL也延用了RECURSIVE这一关键词。
二、实验模拟
1、准备环境
create table tree_data (
id integer,
code text,
pid integer,
sort integer
);
insert into tree_data values(1, '中国', null, 1);
insert into tree_data values(2, '四川', 1, 1);
insert into tree_data values(3, '云南', 1, 2);
insert into tree_data values(4, '成都', 2, 1);
insert into tree_data values(5, '绵阳', 2, 2);
insert into tree_data values(6, '武侯区', 4, 1);
insert into tree_data values(7, '昆明', 3, 1); 2、connectby函数
如果安装了 tablefunc 扩展，就可以使用PG版本的connectby函数。这个没有Oracle那么强大，但是可以满足基本要求。
-- API 如下
connectby(text relname, -- 表名称
text keyid_fld, -- id字段
text parent_keyid_fld -- 父id字段
[, text orderby_fld ], -- 排序字段
text start_with, -- 起始行的id值
int max_depth -- 树深度，0表示无限
[, text branch_delim ]) -- 路径分隔符
-- 基本用法如下，必须通过AS子句定义返回的字段名称和类型
select *
from connectby('demo.tree_data', 'id', 'pid', 'sort', '1', 0, '~')
as (id int, pid int, lvl int, branch text, sort int);PS：虽然通过join可以查询出节点的code，但是branch部分不能直接转换成对应的code，使用上还是不太方便。
3、CTE语法
使用CTE语法，通过 with recursive 来实现树形数据的递归查询。这个方法虽然没有connectby那么直接，但是灵活性和显示效果更好。
with recursive cte as
(
-- 先查询root节点
select
id, code, pid, '' as pcode,
code as branch
from tree_data where id = 1
union all
-- 通过cte递归查询root节点的直接子节点
select
origin.id, origin.code, cte.id as pid, cte.code as pcode,
cte.branch || '~' || origin.code
from cte
join tree_data as origin on origin.pid = cte.id
)
select id,code, pid, pcode, branch,
-- 通过计算分隔符的个数，模拟计算出树形的深度
(length(branch)-length(replace(branch, '~', ''))) as lvl
from cte; 执行过程说明
从上面的例子可以看出，WITH RECURSIVE语句包含了两个部分
- non-recursive term（非递归部分），即上例中的union all前面部分
- recursive term（递归部分），即上例中union all后面部分
执行步骤如下：
- 执行non-recursive term。（如果使用的是union而非union all，则需对结果去重）其结果作为recursive term中对result的引用，同时将这部分结果放入临时的working table中
- 重复执行如下步骤，直到working table为空：用working table的内容替换递归的自引用，执行recursive term，（如果使用union而非union all，去除重复数据），并用该结果（如果使用union而非union all，则是去重后的结果）替换working table
觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~