PostgreSQL中的with递归（兼容oracle connect by）_postgresql connect by

oracle中的树状查询是比较常用的，通过connect by子句来实现。虽然在pg中默认不支持这种语法，但是可以用过with子句来实现类似的功能。
with子句的RECURSIVE选项将WITH从单纯的句法便利变成了一种在标准SQL中不能完成的特 性。通过使用RECURSIVE，一个WITH查询可以引用它自己的输出 。

例如下面这张表tbl：

bill=# select * from tbl;
 id | pid 
----+-----
  1 |   1
  2 |   1
  3 |   1
  4 |   2
  5 |   3
  6 |   4
(6 rows)
1
2
3
4
5
6
7
8
9
10

 我们想要查询id是1的这一行数据的子数据，在oracle中我们可以使用connect by子句，类似这样：
1

select * from tbl as t1
start with t1.pid=1
connect by t1.id = prior t1.pid;
1
2
3

但是在pg中该如何实现呢？这个时候可以使用with recursive语句递归的方法来实现：
1

bill=# with recursive tbl_result as (  
bill(#   select * from tbl where id=1  
bill(#     union all  
bill(#   select t2.* from tbl_result t1 join tbl t2 on t1.id=t2.pid  
bill(# )  
bill-# select * from tbl_result;
 id | pid 
----+-----
  1 |   1
  1 |   1
  2 |   1
  3 |   1
  4 |   2
  5 |   3
  6 |   4
(7 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

一个递归WITH查询的通常形式总是一个非递归项，然后是UNION(或者UNION ALL)，再然后是一个递归项，其中只有递归项能够包含对于查询自身输出的引用。
其大致流程如下：

为了理解pg中的with递归，再举一个最简单的例子：如查询1加到100的总和。这个通过递归可以很方便的实现：

bill=# with recursive t(n) as(
bill(# select 1
bill(# union all
bill(# select n+1 from t where n<100)
bill-# select sum(n) from t;
 sum  
------
 5050
(1 row)
1
2
3
4
5
6
7
8
9

灵活使用with递归，有些场景下会取得意想不到的优化效果，比方说我们需要某稀疏列的唯一值，最容易想到的就是：

select distinct col from table ;  
1

但是这种写法在数据量很大的时候效率很相当低：

bill=# create table sex (sex char(1), otherinfo text);    
CREATE TABLE
bill=# create index idx_sex_1 on sex(sex);    
CREATE INDEX
bill=# insert into sex select 'm', generate_series(1,5000000)||'this is test';    
INSERT 0 5000000
bill=# insert into sex select 'w', generate_series(1,5000000)||'this is test';   
INSERT 0 5000000
1
2
3
4
5
6
7
8

普通的distinct写法(2535.802 ms)：

bill=# select distinct sex from sex;
 sex 
-----
 m
 w
(2 rows)

Time: 2535.802 ms (00:02.536)
1
2
3
4
5
6
7
8

with递归(0.995ms)：

bill=# with recursive skip as (    
bill(#   (    
bill(#     select min(t.sex) as sex from sex t where t.sex is not null    
bill(#   )    
bill(#   union all    
bill(#   (    
bill(#     select (select min(t.sex) as sex from sex t where t.sex > s.sex and t.sex is not null)     
bill(#       from skip s where s.sex is not null   
bill(#   )    
bill(# )     
bill-# select * from skip where sex is not null;   
 sex 
-----
 m
 w
(2 rows)

Time: 0.995 ms
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18