本文转载自：https://www.cnblogs.com/willingtolove/p/10162587.html,作者：willingtolove

正文

　　SQL SERVER 2005之前的版本只能用函数方法实现，SQL SERVER 2005之后新增了CTE功能，可以利用CTE实现递归查询；

　　CTE：公用表达式Common Table Expression 是SQL SERVER 2005版本之后引入的一个特性；

#递归实现Demo

1、根据指定的节点向上获取所有父节点,向下获取所有子节点

 1 --根据指定的节点向下获取所有子节点
 2 with
 3 CTE
 4 as
 5 (
 6     select * from GroupInfo where Id=1
 7     union all
 8     select G.* from CTE inner join GroupInfo as G
 9     on CTE.Id=G.ParentGroupId
10 )
11 select * from CTE order by Id

 1 --根据指定的节点向上获取所有父节点
 2 with
 3 CTE
 4 as
 5 (
 6     select * from GroupInfo where Id=14
 7     union all
 8     select G.* from CTE inner join GroupInfo as G
 9     on CTE.ParentGroupId=G.Id
10 )
11 select * from CTE order by Id

2、构造递归路径

 1 --构造递归路径
 2 with
 3 CTE
 4 as
 5 (
 6     select Id,GroupName,ParentGroupId,GroupPath=CAST( GroupName as nvarchar(max)) from GroupInfo where Id=1
 7     union all
 8     select G.*,CAST(CTE.GroupPath+'//'+G.GroupName as nvarchar(max)) as GroupPath from CTE 
 9     inner join GroupInfo as G
10     on CTE.Id=G.ParentGroupId
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 GroupInfo
 7     WHERE ParentGroupId = 0
 8     UNION ALL
 9     SELECT   GroupInfo.* ,CAST(sort + RIGHT('000' + CAST(GroupInfo.[Id] AS VARCHAR),3) AS VARCHAR(MAX)) AS sort
10     FROM CTE
11     INNER JOIN GroupInfo ON CTE.Id = GroupInfo.ParentGroupId
12 )
13 SELECT * FROM CTE ORDER BY sort 

4、递归层级查询（查询出节点所属的层级）

1 --查询节点层级
2 WITH CTE AS (
3     SELECT *,1 AS [Level] FROM GroupInfo WHERE ParentGroupId=0
4     UNION ALL
5     SELECT G.*,CTE.Level+1 FROM GroupInfo as G 
6     JOIN CTE ON CTE.Id =G.ParentGroupId
7 )
8 SELECT * FROM CTE