PostgreSQL的遞歸查詢(with recursive) · postgresql手冊

開發有需求，說需要對一張地區表進行遞歸查詢，Postgres中有個?**with recursive**的查詢方式，可以滿足遞歸查詢(一般>=2層)。測試如下： ~~~ 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' , '深圳市') ; ~~~ 測試語句,查詢浙江省及以下縣市： ~~~ test=# with RECURSIVE cte as test-# ( test(# select a.id,a.name,a.pid from tb a where id='002' test(# union all test(# select k.id,k.name,k.pid from tb k inner join cte c on c.id = k.pid test(# )select id,name from cte; id | name -----+-------- 002 | 浙江省 003 | 衢州市 004 | 杭州市 005 | 湖州市 006 | 嘉興市 007 | 寧波市 008 | 紹興市 009 | 臺州市 010 | 溫州市 011 | 麗水市 012 | 金華市 013 | 舟山市 014 | 上城區 015 | 下城區 016 | 拱墅區 017 | 余杭區 018 | 金東區 (17 rows) ~~~ 如果查詢有報錯如死循環跳出，則需要檢查下父字段與子字段的數據是否有相同。? 如果想按層次分別顯示出來，也可以這么寫? <table style="BORDER-TOP: rgb(248,248,248) 1px solid; BORDER-RIGHT: rgb(248,248,248) 1px solid; WIDTH: 703px; WHITE-SPACE: normal; WORD-SPACING: 0px; BORDER-COLLAPSE: collapse; BORDER-BOTTOM: rgb(248,248,248) 1px solid; TEXT-TRANSFORM: none; COLOR: rgb(68,68,68); PADDING-BOTTOM: 0px; TEXT-ALIGN: left; PADDING-TOP: 0px; FONT: 12px/22px 微軟雅黑, Verdana, sans-serif, 宋體; PADDING-LEFT: 0px; MARGIN: 0px; BORDER-SPACING: 1px; BORDER-LEFT: rgb(248,248,248) 1px solid; WIDOWS: 1; LETTER-SPACING: normal; PADDING-RIGHT: 0px; BACKGROUND-COLOR: rgb(255,255,255); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" bordercolor="#000000" cellspacing="0" cellpadding="2" border="1"><tbody style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"><tr style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"><td style="BORDER-TOP: rgb(248,248,248) 1px solid; BORDER-RIGHT: rgb(248,248,248) 1px solid; BORDER-BOTTOM: rgb(248,248,248) 1px solid; PADDING-BOTTOM: 1px; PADDING-TOP: 1px; PADDING-LEFT: 2px; MARGIN: 0px; BORDER-LEFT: rgb(248,248,248) 1px solid; PADDING-RIGHT: 2px"> test=# with RECURSIVE cte as ( select a.id,cast(a.name as varchar(100)) from tb a where id='002' union all? 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 )select id,name from cte ; ?id ?| ? ? ? ? name ? ? ? ?? -----+---------------------- ?002 | 浙江省 ?003 | 浙江省>衢州市 ?004 | 浙江省>杭州市 ?005 | 浙江省>湖州市 ?006 | 浙江省>嘉興市 ?007 | 浙江省>寧波市 ?008 | 浙江省>紹興市 ?009 | 浙江省>臺州市 ?010 | 浙江省>溫州市 ?011 | 浙江省>麗水市 ?012 | 浙江省>金華市 ?013 | 浙江省>舟山市 ?014 | 浙江省>杭州市>上城區 ?015 | 浙江省>杭州市>下城區 ?016 | 浙江省>杭州市>拱墅區 ?017 | 浙江省>杭州市>余杭區 ?018 | 浙江省>麗水市>金東區 (17 rows) <span style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; FLOAT: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 12px/22px 微軟雅黑, Verdana, sans-serif, 宋體; WIDOWS: 1; DISPLAY: inline !important; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(255,255,255); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px">PS: MYSQL貌似還沒出這么一種功能，?<br style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); PADDING-BOTTOM: 0px; TEXT-ALIGN: left; PADDING-TOP: 0px; FONT: 12px/22px 微軟雅黑, Verdana, sans-serif, 宋體; PADDING-LEFT: 0px; MARGIN: 0px; WIDOWS: 1; LETTER-SPACING: normal; PADDING-RIGHT: 0px; BACKGROUND-COLOR: rgb(255,255,255); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px"/><span style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; FLOAT: none; COLOR: rgb(51,51,51); TEXT-ALIGN: left; FONT: 12px/22px 微軟雅黑, Verdana, sans-serif, 宋體; WIDOWS: 1; DISPLAY: inline !important; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(255,255,255); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px">附帶說一下SqlServer的的遞歸查詢，語法類似，不過把recursive去掉就可以了，如：<pre style="BORDER-TOP: rgb(221,221,221) 1px solid; BORDER-RIGHT: rgb(221,221,221) 1px solid; BACKGROUND: rgb(246,246,246); WORD-SPACING: 0px; BORDER-BOTTOM: rgb(221,221,221) 1px solid; TEXT-TRANSFORM: none; COLOR: rgb(51,51,51); PADDING-BOTTOM: 5px; TEXT-ALIGN: left; PADDING-TOP: 5px; FONT: 9pt/18px 'Courier New', Arial; PADDING-LEFT: 5px; MARGIN: 10px 0px 10px 20px; BORDER-LEFT: rgb(108,226,108) 5px solid; WIDOWS: 1; LETTER-SPACING: normal; PADDING-RIGHT: 5px; TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px">with cte as  (  select a.id,a.name,a.pid from tb a where id='002'  union all  select k.id,k.name,k.pid from tb k inner join cte c on c.id = k.pid  )select id,name from cte;</pre></td></tr></tbody></table>