postgres WITH 查询

01，WITH 查询　

ITH查询是PostgreSQL支持的高级SQL特性之一，这一特性常称为CTE(CommonTable Expressions), WITH查询在复杂查询中定义一个辅助语句
（可理解成在一个查询中定义的临时表），这一特性常用于复杂查询或递归查询应用场景。

02，实例去了解WITH

例1：

kingledb=> WITH t as (
kingledb(> SELECT generate_series(1,3)
kingledb(> ) SELECT * from t;
 generate_series
-----------------
               1
               2
               3
(3 rows)

Time: 1.046 ms


-- 这个就是简单的应用  with 辅助取值

例2：

kingledb=> WITH RECURSIVE t(x) as (
kingledb(> SELECT 1
kingledb(> UNION
kingledb(> SELECT x + 1
kingledb(>   FROM t
kingledb(>  WHERE x < 5
kingledb(> )
kingledb-> SELECT sum(x) FROM t;
 sum
-----
  15
(1 row)

Time: 1.249 ms
-- 经典的递归查询取和

例3：
    来自：https://my.oschina.net/Kenyon/blog/55137

create table tb(id varchar(3) , pid varchar(3) , name varchar(10)); 

insert into tb values('002' , 0 , '浙江省'); 
insert into tb values('001' , 0 , '广东省'); 
insert into tb values('003' , '002' , '衢州市');  
insert into tb values('004' , '002' , '杭州市') ; 
insert into tb values('005' , '002' , '湖州市');  
insert into tb values('006' , '002' , '嘉兴市') ; 
insert into tb values('007' , '002' , '宁波市');  
insert into tb values('008' , '002' , '绍兴市') ; 
insert into tb values('009' , '002' , '台州市');  
insert into tb values('010' , '002' , '温州市') ; 
insert into tb values('011' , '002' , '丽水市');  
insert into tb values('012' , '002' , '金华市') ; 
insert into tb values('013' , '002' , '舟山市');  
insert into tb values('014' , '004' , '上城区') ; 
insert into tb values('015' , '004' , '下城区');  
insert into tb values('016' , '004' , '拱墅区') ; 
insert into tb values('017' , '004' , '余杭区') ; 
insert into tb values('018' , '011' , '金东区') ; 
insert into tb values('019' , '001' , '广州市') ; 
insert into tb values('020' , '001' , '深圳市') ;


kingledb=> with RECURSIVE cte as
kingledb-> (
kingledb(> select a.id,cast(a.name as varchar(100)) from tb a where id='002'
kingledb(> union all
kingledb(> select k.id,cast(c.name||'>'||k.name as varchar(100)) as name  from tb k inner join cte c on c.id = k.pid
kingledb(> )select id,name from cte ;
 id  |         name
-----+----------------------
 002 | 浙江省
 003 | 浙江省>衢州市
 004 | 浙江省>杭州市
 005 | 浙江省>湖州市
 006 | 浙江省>嘉兴市
 007 | 浙江省>宁波市
 008 | 浙江省>绍兴市
 009 | 浙江省>台州市
 010 | 浙江省>温州市
 011 | 浙江省>丽水市
 012 | 浙江省>金华市
 013 | 浙江省>舟山市
 014 | 浙江省>杭州市>上城区
 015 | 浙江省>杭州市>下城区
 016 | 浙江省>杭州市>拱墅区
 017 | 浙江省>杭州市>余杭区
 018 | 浙江省>丽水市>金东区


--递归查询对应的父节点

 

例4：
CREATE TABLE test_area(id int4,name varchar(32),fatherid int4);
INSERT INTO test_area VALUES (1, '中国',0);
INSERT INTO test_area VALUES (2,'辽宁',1);
INSERT INTO test_area VALUES (3, '山东',1);
INSERT INTO test_area VALUES (4,'沈阳',2);
INSERT INTO test_area VALUES (5, '大连',2); 
INSERT INTO test_area VALUES (6,'济南',3); 
INSERT INTO test_area VALUES (7,'和平区',4);
INSERT INTO test_area VALUES (8,'沈河区',4);

WITH RECURSIVE r as (
    SELECT * from test_area WHERE id = 5
    UNION
    SELECT test_area.* from test_area , r WHERE test_area.id = r.fatherid
) SELECT * from r ORDER BY ID;