432
PostgreSQL Oracle 兼容性係列之 - WITH 遞歸 ( connect by )
( 請把本文某些SQL的 SELEC改成 s e l e c t , FRO改成 f r o m , WHE改成 w h e r e 不要空格)
connect by語法是Oracle用來實現樹形查詢的一種語法。
應用場景如圖：
圖1
PostgreSQL雖然不支持connect by語法，但是支持with recursive語法，可以達到相同的目的。
下麵舉個例子來說明with recursive的用法。
如圖：
圖1
假如2(t), 3(t), 4(f), 5(t), 6(f),
當輸入條件為2並且附加條件為t時，需要查出2(t),3(t),4(f)
其實這個查詢包含了樹形查詢，同時還包含了第二個條件過濾。
表結構:
digoal=> \d tbl_role
Table "digoal.tbl_role"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | -- 唯一ID
code | character varying(32) | -- 樹形結構代碼
extend | boolean | -- 是否可擴展
數據:
digoal=> SELEC * FRO tbl_role order by code;
id | code | extend
----+--------------+--------
1 | 001 | t
4 | 001001 | t
5 | 001002 | f
6 | 001003 | f
7 | 001003001 | t
10 | 001005 | t
11 | 001005001 | f
11 | 001005001001 | t
2 | 002 | t
8 | 002001 | f
3 | 003 | t
9 | 003001 | f
(12 rows)
思路:
因為這個CASE設計的數型數據存儲在一個字段code裏麵，所以首先要把對應的父級找出來，才好去做子級和父級的關聯查詢.
然後使用遞歸查詢得出所要的結果.
具體實現:
例如要查詢code = '001',包含所有的擴展結果, (遇到extend = f時僅取出本條，下級的數據不取出) :
digoal=> with recursive sub as
(
SELEC id,code,coalesce(case when length(substring(code,1,length(code)-3))=0 then null else substring(code,1,length(code)-3) end,'root') parcode,extend
FRO tbl_role WHE code ='001'
union
SELEC d.* FRO (SELEC id,code,coalesce(case when length(substring(code,1,length(code)-3))=0 then null else substring(code,1,length(code)-3) end,'root') parcode,extend
FRO tbl_role WHE code like '001%') as d
join
sub as sd
on (d.parcode=sd.code and (sd.extend='t' or sd.parcode='root'))
)
SELEC * FRO sub;
id | code | parcode | extend
----+-----------+---------+--------
1 | 001 | root | t
4 | 001001 | 001 | t
5 | 001002 | 001 | f
6 | 001003 | 001 | f
10 | 001005 | 001 | t
11 | 001005001 | 001005 | f
(6 rows)
換個取code = '001005'
digoal=> with recursive sub as
(
SELEC id,code,coalesce(case when length(substring(code,1,length(code)-3))=0 then null else substring(code,1,length(code)-3) end,'root') parcode,extend
FRO tbl_role WHE code ='001005'
union
SELEC d.* FRO (SELEC id,code,coalesce(case when length(substring(code,1,length(code)-3))=0 then null else substring(code,1,length(code)-3) end,'root') parcode,extend
FRO tbl_role WHE code like '001005%') as d
join
sub as sd
on (d.parcode=sd.code and (sd.extend='t' or sd.parcode='root'))
)
SELEC * FRO sub;
id | code | parcode | extend
----+-----------+---------+--------
10 | 001005 | 001 | t
11 | 001005001 | 001005 | f
(2 rows)
多個code的場景
digoal=> with recursive sub as
(
SELEC id,code,coalesce(case when length(substring(code,1,length(code)-3))=0 then null else substring(code,1,length(code)-3) end,'root') parcode, extend
FRO tbl_role WHE code in ('001005','001003')
union
SELEC d.* FRO (SELEC id,code,coalesce(case when length(substring(code,1,length(code)-3))=0 then null else substring(code,1,length(code)-3) end,'root') parcode,extend
FRO tbl_role WHE (code like '001005%' or code like '001003%')) as d
join
sub as sd
on (d.parcode=sd.code and (sd.extend='t' or sd.parcode='root'))
)
SELEC * FRO sub;
id | code | parcode | extend
----+-----------+---------+--------
6 | 001003 | 001 | f
10 | 001005 | 001 | t
11 | 001005001 | 001005 | f
(3 rows)
小結：
PostgreSQL的with recursive查詢提供了類似ORACLE的
[ START WITH condition ] CONNECT BY [ NOCYCLE ] condition
的異構查詢功能。
參考 :
https://wiki.postgresql.org/wiki/CTEReadme
https://www.postgresql.org/docs/9.0/static/queries-with.html
補充,另外一個更簡易的樹形查詢的例子:
TABLE:
postgres=# \d tbl_menu
Table "public.tbl_menu"
Column | Type | Modifiers
------------+-----------------------------+---------------------------
id | bigint | not null
name | character varying(50) | not null
parentid | bigint | not null
type | integer | not null
status | integer | not null
grade | integer | not null
filename | character varying(50) |
md5 | character varying(50) |
brief | character varying(500) |
orderid | bigint | not null default 99999999
updatetime | timestamp without time zone |
createtime | timestamp without time zone | default now()
DATA:
postgres=# SELEC * FRO tbl_menu;
id | name | parentid | type | status | grade | filename | md5 | brief | orderid | updateti
me | createtime
----+--------+----------+------+--------+-------+----------+----------------------------------+----------+----------+---------------
----------+----------------------------
18 | 言情 | 14 | 4 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 2 | 2011-05-19 14:
41:17.287 | 2011-05-19 14:37:44.11
19 | 玄幻 | 14 | 4 | 1 | 24 | 1.png | ad5df343eb6b83d3e1100bdbccf98264 | | 3 | 2011-05-19 14:
41:25.287 | 2011-05-19 14:38:06.39
20 | 明星 | 13 | 3 | 1 | 24 | 1.png | ad5df343eb6b83d3e1100bdbccf98264 | | 99999999 | 2011-05-19 15:
39:24.118 | 2011-05-19 15:34:38.719
21 | 免費 | 12 | 2 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 99999999 | 2011-05-19 20:
06:30.016 | 2011-05-19 20:06:30.016
22 | 專輯 | 11 | 1 | 1 | 24 | 1.png | ad5df343eb6b83d3e1100bdbccf98264 | | 99999999 | 2011-05-19 20:
06:43.328 | 2011-05-19 20:06:43.328
25 | 複古 | 22 | 1 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 1 | 2011-05-19 21:
45:47.459 | 2011-05-19 21:45:47.459
24 | 流行 | 22 | 1 | 1 | 26 | 1.png | ad5df343eb6b83d3e1100bdbccf98264 | 流行音樂 | 2 | 2011-05-19 21:
45:54.365 | 2011-05-19 21:41:51.749
35 | 高清 | 33 | 6 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 1 | 2011-05-23 14:
07:44.659 | 2011-05-23 14:07:44.659
36 | 明星 | 33 | 6 | 1 | 26 | | d41d8cd98f00b204e9800998ecf8427e | | 2 | 2011-05-23 14:
08:04.175 | 2011-05-23 14:08:04.175
37 | 浙江 | 34 | 5 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 1 | 2011-05-23 14:
11:56.77 | 2011-05-23 14:11:56.77
11 | 音樂 | 0 | 1 | 1 | 24 | | | | 1 | 2011-05-23 14:
12:08.411 | 2011-05-19 14:25:24.966436
12 | 視頻 | 0 | 2 | 1 | 24 | | | | 2 | 2011-05-23 14:
12:17.192 | 2011-05-19 14:25:37.569062
13 | 圖酷 | 0 | 3 | 1 | 24 | | | | 3 | 2011-05-23 14:
12:22.896 | 2011-05-19 14:25:47.118481
14 | 書籍 | 0 | 4 | 1 | 24 | | | | 4 | 2011-05-23 14:
12:29.317 | 2011-05-19 14:26:11.781762
34 | 直播 | 0 | 5 | 1 | 24 | | | | 5 | 2011-05-23 14:
12:34.786 | 2011-05-19 14:25:24.966436
33 | 點播 | 0 | 6 | 1 | 24 | | | | 6 | 2011-05-23 14:
12:38.567 | 2011-05-19 14:25:24.966436
38 | 北京 | 34 | 5 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 1 | 2011-05-23 14:
13:37.162 | 2011-05-23 14:13:37.162
39 | 漫畫 | 13 | 3 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 1 | 2011-05-23 14:
28:56.777 | 2011-05-23 14:28:56.777
40 | 劉德華 | 25 | 1 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 1 | 2011-05-25 15:
36:33.139 | 2011-05-25 15:36:33.139
(19 rows)
向上查詢:
with recursive t_result as
(
SELEC id,name,parentid,type,status,grade,filename,md5,brief,orderid,updatetime,createtime
FRO tbl_menu as t_initial where name ~ '劉德華'
union
SELEC t_working.id,t_working.name,t_working.parentid,t_working.type,t_working.status,t_working.grade,t_working.filename,t_working.md5,t_working.brief,t_working.orderid,t_working.updatetime,t_working.createtime
FRO tbl_menu as t_working
join
t_result
on (t_result.parentid=t_working.id)
)
SELEC id,name,parentid,type,status,grade,filename,md5,brief,orderid,updatetime,createtime
FRO t_result;
id | name | parentid | type | status | grade | filename | md5 | brief | orderid | updatetime
| createtime
----+--------+----------+------+--------+-------+----------+----------------------------------+-------+----------+------------------
-------+----------------------------
40 | 劉德華 | 25 | 1 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 1 | 2011-05-25 15:36:
33.139 | 2011-05-25 15:36:33.139
25 | 複古 | 22 | 1 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 1 | 2011-05-19 21:45:
47.459 | 2011-05-19 21:45:47.459
22 | 專輯 | 11 | 1 | 1 | 24 | 1.png | ad5df343eb6b83d3e1100bdbccf98264 | | 99999999 | 2011-05-19 20:06:
43.328 | 2011-05-19 20:06:43.328
11 | 音樂 | 0 | 1 | 1 | 24 | | | | 1 | 2011-05-23 14:12:
08.411 | 2011-05-19 14:25:24.966436
(4 rows)
向下查詢:
with recursive t_result as
(
SELEC id,name,parentid,type,status,grade,filename,md5,brief,orderid,updatetime,createtime
FRO tbl_menu as t_initial where name ~ '音樂'
union
SELEC t_working.id,t_working.name,t_working.parentid,t_working.type,t_working.status,t_working.grade,t_working.filename,t_working.md5,t_working.brief,t_working.orderid,t_working.updatetime,t_working.createtime
FRO tbl_menu as t_working
join
t_result
on (t_working.parentid=t_result.id)
)
SELEC id,name,parentid,type,status,grade,filename,md5,brief,orderid,updatetime,createtime
FRO t_result;
id | name | parentid | type | status | grade | filename | md5 | brief | orderid | updateti
me | createtime
----+--------+----------+------+--------+-------+----------+----------------------------------+----------+----------+---------------
----------+----------------------------
11 | 音樂 | 0 | 1 | 1 | 24 | | | | 1 | 2011-05-23 14:
12:08.411 | 2011-05-19 14:25:24.966436
22 | 專輯 | 11 | 1 | 1 | 24 | 1.png | ad5df343eb6b83d3e1100bdbccf98264 | | 99999999 | 2011-05-19 20:
06:43.328 | 2011-05-19 20:06:43.328
25 | 複古 | 22 | 1 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 1 | 2011-05-19 21:
45:47.459 | 2011-05-19 21:45:47.459
24 | 流行 | 22 | 1 | 1 | 26 | 1.png | ad5df343eb6b83d3e1100bdbccf98264 | 流行音樂 | 2 | 2011-05-19 21:
45:54.365 | 2011-05-19 21:41:51.749
40 | 劉德華 | 25 | 1 | 1 | 24 | | d41d8cd98f00b204e9800998ecf8427e | | 1 | 2011-05-25 15:
36:33.139 | 2011-05-25 15:36:33.139
(5 rows)
最後用兩幅圖說明with recursive的原理，學會了是很好用的。
圖2
圖3
PostgreSQL針對這種樹形查詢，提供了一種數據類型較ltree，使用起來非常方便，如果你有這種應用場景的需求，也可以考慮一下ltree。
圖4
圖1
圖2
圖3
圖4
最後更新：2017-04-01 13:44:33