postgresql的WITH RECURSIVE实现递归
程序员文章站
2022-03-29 20:25:06
原文地址:http://www.jasongj.com/sql/cte/with as可以把结果集暂存,下面两个sql的结果是一样的(顺序不一样)with r1 as (select * from users),r2 as (select * from employees)select r2.* from r1 inner join r2 on r1.user_pk=r2.user_pk where r1.user_type<>'1'select employees.* from...
原文地址:http://www.jasongj.com/sql/cte/
with as可以把结果集暂存,下面两个sql的结果是一样的(顺序不一样)
with r1 as (select * from users),r2 as (select * from employees)
select r2.* from r1 inner join r2 on r1.user_pk=r2.user_pk
where r1.user_type<>'1'
select employees.* from users inner join employees on users.user_pk=employees.user_pk
where users.user_type<>'1'
递归查询
组织表有如下数据:
id=10的数据的亲(上级)组织的id是70645
count | id | pid | name |
---|---|---|---|
12 | 10 | 70645 | 組織10 |
12 | 70635 | null | 組織1 |
12 | 70645 | null | 組織9 |
12 | 70636 | 70635 | 組織2 |
12 | 70637 | 70636 | 組織3 |
12 | 70638 | 70637 | 組織4 |
12 | 70639 | 70638 | 組織5 |
12 | 70640 | 70635 | 組織6 |
12 | 70641 | 70640 | 組織7 |
12 | 71113 | null | 組職名 |
12 | 21001 | null | 組織名(日)01 |
12 | 20002 | null | 組織名(日)02 |
WITH RECURSIVE results AS
(
SELECT
id,pid,name
FROM organizations
WHERE id = 70639
UNION ALL --注释all可以去重
SELECT
org.id,org.pid,org.name
FROM results
inner JOIN organizations org
ON results.pid = org.id
)
SELECT
count(1) over(),*
FROM results;
上面的sql可以查询id = 70639的组织和其所有亲组织,结果如下
count | id | pid | name |
---|---|---|---|
5 | 70639 | 70638 | 組織5 |
5 | 70638 | 70637 | 組織4 |
5 | 70637 | 70636 | 組織3 |
5 | 70636 | 70635 | 組織2 |
5 | 70635 | null | 組織1 |
--可能报错type character varying overall,加上::varchar(100)统一字段类型
WITH RECURSIVE results AS
(
SELECT
id,pid,name::varchar(100),1 num
FROM organizations
where pid is null
UNION ALL
SELECT
org.id,org.pid,(results.name||'>'||org.name)::varchar(100) name,1+results.num num
FROM results
inner JOIN organizations org
ON results.id = org.pid
--where results.num<3
)
SELECT
count(1) over(),*
FROM results;
上面的sql可以从上往下(广度优先)查询所有组织,num是当前组织的层数,结果如下
count | id | pid | name | num |
---|---|---|---|---|
12 | 70635 | null | 組織1 | 1 |
12 | 70645 | null | 組織9 | 1 |
12 | 71113 | null | 組職名 | 1 |
12 | 21001 | null | 組織名(日)01 | 1 |
12 | 20002 | null | 組織名(日)02 | 1 |
12 | 70640 | 70635 | 組織1>組織6 | 2 |
12 | 70636 | 70635 | 組織1>組織2 | 2 |
12 | 10 | 70645 | 組織9>組織10 | 2 |
12 | 70641 | 70640 | 組織1>組織6>組織7 | 3 |
12 | 70637 | 70636 | 組織1>組織2>組織3 | 3 |
12 | 70638 | 70637 | 組織1>組織2>組織3>組織4 | 4 |
12 | 70639 | 70638 | 組織1>組織2>組織3>組織4>組織5 | 5 |
如果只需要检索最上面3层的组织,加上where results.num<3即可,结果如下
count | id | pid | name | num |
---|---|---|---|---|
10 | 70635 | null | 組織1 | 1 |
10 | 70645 | null | 組織9 | 1 |
10 | 71113 | null | 組職名 | 1 |
10 | 21001 | null | 組織名(日)01 | 1 |
10 | 20002 | null | 組織名(日)02 | 1 |
10 | 70640 | 70635 | 組織1>組織6 | 2 |
10 | 70636 | 70635 | 組織1>組織2 | 2 |
10 | 10 | 70645 | 組織9>組織10 | 2 |
10 | 70641 | 70640 | 組織1>組織6>組織7 | 3 |
10 | 70637 | 70636 | 組織1>組織2>組織3 | 3 |
WITH RECURSIVE 执行过程 从上面的例子可以看出,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
本文地址:https://blog.csdn.net/weixin_43292547/article/details/107405201
下一篇: 变量命名的方法及使用