#遞歸實現Demo
1、根據指定的節點向上獲取所有父節點,向下獲取所有子節點
復制代碼
1 --根據指定的節點向下獲取所有子節點
2 with
3 CTE
4 as
5 (
6 select * from huiyuan where Id=1
7 union all
8 select G.* from CTE inner join huiyuan as G
9 on CTE.Id=G.sj_id
10 )
11 select * from CTE order by Id
復制代碼
復制代碼
1 --根據指定的節點向上獲取所有父節點
2 with
3 CTE
4 as
5 (
6 select * from huiyuan where Id=14
7 union all
8 select G.* from CTE inner join huiyuan as G
9 on CTE.sj_id=G.Id
10 )
11 select * from CTE order by Id
復制代碼
2、構造遞歸路徑
復制代碼
1 --構造遞歸路徑
2 with
3 CTE
4 as
5 (
6 select Id,GroupName,sj_id,GroupPath=CAST( GroupName as nvarchar(max)) from huiyuan where Id=1
7 union all
8 select G.*,CAST(CTE.GroupPath+'//'+G.GroupName as nvarchar(max)) as GroupPath from CTE
9 inner join huiyuan as G
10 on CTE.Id=G.sj_id
11 )
12 select * from CTE
復制代碼
3、分組遞歸，將同一條分支上節點放到一起
復制代碼
1 --通過id字段的字符串的拼接，形成sort字段，再通過sort排序，來實現同一分支上的節點放到一起
2 WITH
3 CTE
4 AS
5 (
6 SELECT * ,CAST(RIGHT('000' + CAST([Id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort FROM huiyuan
7 WHERE sj_id = 0
8 UNION ALL
9 SELECT huiyuan.* ,CAST(sort + RIGHT('000' + CAST(huiyuan.[Id] AS VARCHAR),3) AS VARCHAR(MAX)) AS sort
10 FROM CTE
11 INNER JOIN huiyuan ON CTE.Id = huiyuan.sj_id
12 )
13 SELECT * FROM CTE ORDER BY sort
復制代碼
4、遞歸層級查詢（查詢出節點所屬的層級）
復制代碼
1 --查詢節點層級
2 WITH CTE AS (
3 SELECT *,1 AS [Level] FROM huiyuan WHERE sj_id=0
4 UNION ALL
5 SELECT G.*,CTE.Level+1 FROM huiyuan as G
6 JOIN CTE ON CTE.Id =G.sj_id
7 )
8 SELECT * FROM CTE
復制代碼
