PostgreSQL 之 with查询

作者：瀚高PG实验室 （Highgo PG Lab）-波罗
WITH语句通常被称为通用表表达式（Common Table Expressions）或者CTEs。
WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。
这些语句通常被称为公共表表达式或CTE（Common table expression）
它们可以被看成是定义只在一个查询中存在的临时表。
在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。

示例如下：

1. 建表

postgres=# create table cte_t(id serial primary key,name character varying, parentid integer);  
CREATE TABLE  

postgres=# \d cte_t  
                               Table "public.cte_t"  
  Column  |       Type        |                    Modifiers                       
----------+-------------------+--------------------------------------------------  
 id       | integer           | not null default nextval('cte_t_id_seq'::regclass)  
 name     | character varying |   
 parentid | integer           |   
Indexes:  
    "cte_t_pkey" PRIMARY KEY, btree (id)  

2. 插入测试数据

postgres=# insert into cte_t values(generate_series(1,5),'john',0);  
INSERT 0 5  
postgres=# insert into cte_t values(6,'john1',1);  
INSERT 0 1  
postgres=# insert into cte_t values(7,'john2',1);  
INSERT 0 1  
postgres=# insert into cte_t values(8,'john11',6);  
INSERT 0 1  

postgres=# select * from cte_t;  
 id |  name  | parentid   
----+--------+----------  
  1 | john   |        0  
  2 | john   |        0  
  3 | john   |        0  
  4 | john   |        0  
  5 | john   |        0  
  6 | john1  |        1  
  7 | john2  |        1  
  8 | john11 |        6  
(8 rows)  

3. with子句

postgres=# with t as (select * from cte_t where parentid=1) select count(0) from t;  
 count   
-------  
     2  
(1 row)  

postgres=# with t(a,b,c) as (select * from cte_t where parentid=1) select a,b,c from t;  
 a |   b   | c   
---+-------+---  
 6 | john1 | 1  
 7 | john2 | 1  
(2 rows)  

4. 多个with子句的结合使用
   parentid=1的记录的所有子记录

postgres=# with t1 as (select * from cte_t),t2 as(select * from cte_t where parentid=1) select t1.* from t1,t2 where t2.id=t1.parentid;  
 id |  name  | parentid   
----+--------+----------  
  8 | john11 |        6  
(1 row)  

5. 递归

id为1的记录的所有子记录
postgres=# with recursive t as(select id,name,parentid from cte_t where id=1 union all select k.id,k.name,k.parentid from cte_t k,t where t.id=k.parentid) select * from t;  
 id |  name  | parentid   
----+--------+----------  
  1 | john   |        0  
  6 | john1  |        1  
  7 | john2  |        1  
  8 | john11 |        6  
  9 | john21 |        7  
(5 rows)