|
SQL SERVER 2005之前的版本只能用函数方法实现,SQL SERVER 2005之后新增了CTE功能,可以利用CTE实现递归查询;
CTE:公用表达式Common Table Expression 是SQL SERVER 2005版本之后引入的一个特性;
#填充测试数据
1、sql
- Create table GroupInfo([Id] int,[GroupName] nvarchar(50),[ParentGroupId] int)
- Insert GroupInfo
- select 0,'某某大学',null union all
- select 1,'外语学院',0 union all
- select 2,'英语专业',1 union all
- select 3,'日语专业',1 union all
- select 4,'英语专业一班',2 union all
- select 5,'英语专业二班',2 union all
- select 6,'日语专业一班',3 union all
- select 7,'日语专业二班',3 union all
- select 8, '法学院',0 union all
- select 9, '刑法学专业',8 union all
- select 10,'经济法学专业',8 union all
- select 11,'刑法学专业一班',9 union all
- select 12,'刑法学专业二班',9 union all
- select 13,'经济法学专业一班',10 union all
- select 14,'经济法学专业二班',10
复制代码
2、效果图
#递归实现Demo
1、根据指定的节点向上获取所有父节点,向下获取所有子节点
- --根据指定的节点向下获取所有子节点
- with
- CTE
- as
- (
- select * from GroupInfo where Id=1
- union all
- select G.* from CTE inner join GroupInfo as G
- on CTE.Id=G.ParentGroupId
- )
- select * from CTE order by Id
复制代码
- --根据指定的节点向上获取所有父节点
- with
- CTE
- as
- (
- select * from GroupInfo where Id=14
- union all
- select G.* from CTE inner join GroupInfo as G
- on CTE.ParentGroupId=G.Id
- )
- select * from CTE order by Id
复制代码
2、构造递归路径
- --构造递归路径
- with
- CTE
- as
- (
- select Id,GroupName,ParentGroupId,GroupPath=CAST( GroupName as nvarchar(max)) from GroupInfo where Id=1
- union all
- select G.*,CAST(CTE.GroupPath+'//'+G.GroupName as nvarchar(max)) as GroupPath from CTE
- inner join GroupInfo as G
- on CTE.Id=G.ParentGroupId
- )
- select * from CTE
复制代码
3、分组递归,将同一条分支上节点放到一起
- --通过id字段的字符串的拼接,形成sort字段,再通过sort排序,来实现同一分支上的节点放到一起
- WITH
- CTE
- AS
- (
- SELECT * ,CAST(RIGHT('000' + CAST([Id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort FROM GroupInfo
- WHERE ParentGroupId = 0
- UNION ALL
- SELECT GroupInfo.* ,CAST(sort + RIGHT('000' + CAST(GroupInfo.[Id] AS VARCHAR),3) AS VARCHAR(MAX)) AS sort
- FROM CTE
- INNER JOIN GroupInfo ON CTE.Id = GroupInfo.ParentGroupId
- )
- SELECT * FROM CTE ORDER BY sort
复制代码
4、递归层级查询(查询出节点所属的层级)
- --查询节点层级
- WITH CTE AS (
- SELECT *,1 AS [Level] FROM GroupInfo WHERE ParentGroupId=0
- UNION ALL
- SELECT G.*,CTE.Level+1 FROM GroupInfo as G
- JOIN CTE ON CTE.Id =G.ParentGroupId
- )
- SELECT * FROM CTE
复制代码
|
|