php递归tree技巧_详解PostgreSQL数据库树形结构的递归查询

Oracle供应的connect by扩展语法，大略好用。
那么，PG又是怎么实现呢？

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_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

该例中，定义了两个WITH赞助语句，regional_sales和top_regions。
前者算出每个区域的总发卖量，后者了查出所有发卖量占所有地区总发卖里10%以上的区域。
主语句通过将这个CTEs及订单表关联，算出了顶级区域每件商品的发卖量和发卖额。

2、WITH RECURSIVEWITH语句还可以通过增加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 lvlfrom cte;

实行过程解释

从上面的例子可以看出，WITH RECURSIVE语句包含了两个部分

实行步骤如下：

以为有用的朋友多帮忙转发哦！
后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~