postgreSQL with子句學習

hatch發表於2018-07-02
SQL

With語句是為龐大的查詢語句提供了輔助的功能。 這些語句通常是引用了表表示式或者CTEs(一種臨時資料的儲存方式)，可以看做是一個查詢語句的臨時表。

在With語句中可以使用select，insert，update，delete語句。當然with也可以看成是一個單獨的語句。

測試表資料指令碼

CREATE TABLE public.tb (
	id varchar(3) NULL,
	pid varchar(3) NULL,
	"name" varchar(10) NULL
)
WITH (
	OIDS=FALSE
) ;


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' , '深圳市') ;

複製程式碼

With中使用select

統計pid為0的省份

with t as (select * from public.tb where pid = '0') select count(0) from t;
複製程式碼

查詢記錄結果為2

With語句的遞迴語句

查詢浙江省及以下縣市

with recursive cte as 
(select a.id,a.name, a.pid from public.tb a where id = '002'
union all select k.id,k.name,k.pid from public.tb k inner join cte c on c.id = k.pid) 
select id,name from cte;
複製程式碼

查詢根省份

with recursive p as
(select a.* from public.tb a where a.id = '016' 
union all select b.* from public.tb b,p where b.id = p.pid)
select * from p where p.pid = '0';
複製程式碼

With語句的使用操作語句

就是在with語句中進行資料的insert，update，delete操作。

基本的結構是：


With temp as (
Delete from table_name where sub_clause
    Returning *
)select * from temp
複製程式碼

這裡的returning是不能缺的，缺了不但返回不了資料還會報錯

其中“*”表示所有欄位，如果只需要返回部分欄位那麼可以指定欄位。

相關文章