sql server利用cte递归查询

浏览数：17 / 时间：2015年06月12日

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 cte

result：
id               name           parent_id
2    巴中市    1
4    巴州区    2
5    通江县    2
6    平昌县    2
13    大寨乡    6
14    响滩镇    6
15    龙岗镇    6
16    白衣镇    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 cte

result:
id               name           parent_id     lev
3    达州市    1    1
7    通川区    3    2
8    宣汉县    3    2
9    塔河乡    8    3
10    三河乡    8    3
11    胡家镇    8    3
12    南坝镇    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 cte

result:
id                name          parent_id    rootid          rootname
3    达州市    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 cte

with 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 cte

result:
id                name          pathname
3    达州市    达州市
7    通川区    达州市_通川区
8    宣汉县    达州市_宣汉县
9    塔河乡    达州市_宣汉县_塔河乡
10    三河乡    达州市_宣汉县_三河乡
11    胡家镇    达州市_宣汉县_胡家镇
12    南坝镇    达州市_宣汉县_南坝镇