postgresql的WITH RECURSIVE实现递归

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