sql server利用cte遞迴查詢

標籤：style   blog   color   io   ar   strong   sp   資料   div   

• 1.資料環境準備

           參考Oracle遞迴查詢文章。

• 2.查詢某個節點下的所有子節點

 with cte(id,name,parent_id) as  (    select id,name,parent_id from SC_DISTRICT where name=‘巴中市‘   union all     select sd.id,sd.name,sd.parent_id  from SC_DISTRICT sd ,cte c where c.id = sd.parent_id  )select * from cteresult：id               name           parent_id2    巴中市    14    巴州區    25    通江縣    26    平昌縣    213    大寨鄉    614    響灘鎮    615    龍崗鎮    616    白衣鎮    6

•  3.計算層級（類似Oracle的level偽列）

with cte(id,name,parent_id,lev) as(   select id,name,parent_id,1 from SC_DISTRICT where name=‘達州市‘   union all   select sd.id,sd.name,sd.parent_id,c.lev+1 from SC_DISTRICT sd,cte c where c.id=sd.parent_id)select * from cteresult:id               name           parent_id     lev3    達州市    1    17    通川區    3    28    宣漢縣    3    29    塔河鄉    8    310    三河鄉    8    311    胡家鎮    8    312    南壩鎮    8    3

•  4.查詢路徑的根節點（類似Oracle的connect_by_root）

with cte(id,name,parent_id,rootid,rootname) as(   select id,name,parent_id,id rootid,name rootname from SC_DISTRICT where name=‘達州市‘   union all   select sd.id,sd.name,sd.parent_id,cte.rootid,cte.rootname from SC_DISTRICT sd,cte where sd.parent_id=cte.id)select * from cteresult:id                name          parent_id    rootid          rootname3    達州市    1    3    達州市7    通川區    3    3    達州市8    宣漢縣    3    3    達州市9    塔河鄉    8    3    達州市10    三河鄉    8    3    達州市11    胡家鎮    8    3    達州市12    南壩鎮    8    3    達州市

•  5.查詢遞迴路徑（類似Oracle的sys_connect_by_path）

with cte(id,name,pathname) as(   select id,name,cast(name as nvarchar)  from SC_DISTRICT where name=‘達州市‘   union all   select sd.id,sd.name,cast(cte.pathname+‘_‘+sd.name as nvarchar)  from SC_DISTRICT sd,cte where sd.parent_id=cte.id)select * from ctewith cte(id,name,pathname) as(   select id,name,convert(nvarchar,name)  from SC_DISTRICT where name=‘達州市‘   union all   select sd.id,sd.name,convert(nvarchar,cte.pathname+‘_‘+sd.name)  from SC_DISTRICT sd,cte where sd.parent_id=cte.id)select * from cteresult:id                name          pathname3    達州市    達州市7    通川區    達州市_通川區8    宣漢縣    達州市_宣漢縣9    塔河鄉    達州市_宣漢縣_塔河鄉10    三河鄉    達州市_宣漢縣_三河鄉11    胡家鎮    達州市_宣漢縣_胡家鎮12    南壩鎮    達州市_宣漢縣_南壩鎮

 

sql server利用cte遞迴查詢