当前位置： 移动技术网 > IT编程>数据库>MSSQL > postgresql的WITH RECURSIVE实现递归

postgresql的WITH RECURSIVE实现递归

2020年07月18日  | 移动技术网IT编程  | 我要评论

原文地址：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

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网