sql 函数实现三种父子递归

发布时间：2020-12-12 09:04:06 所属栏目：MsSql教程来源：网络整理

导读：在实际运用中经常会创建这样的结构表Category(Id,ParentId,Name)，特别是用于树形结构时（菜单树，权限树..），这种表设计自然而然地会用到递归，若是在程序中进行递归（虽然在程序中递归真的更方便一些），无论是通过ADO.NET简单sql查找还是ORM属性关联都会

在实际运用中经常会创建这样的结构表Category(Id,ParentId,Name)，特别是用于树形结构时（菜单树，权限树..），这种表设计自然而然地会用到递归，若是在程序中进行递归（虽然在程序中递归真的更方便一些），无论是通过ADO.NET简单sql查找还是ORM属性关联都会执行多次sql语句，难免会造成一些性能上的损耗，所以干脆使用sql的函数来解决这个问题，用函数返回我们最终需要的结果。

针对这类需求，这里我列出三种常用的递归：

以一个节点为基点，列出所有子节点直到无子（找下级）。这有点儿像点兵点将，主帅只有一个，下面是左将、右将，左将下面又有千夫长、百夫长，点兵时主帅下令集合，下面的将军只管各自的队伍。
以一个节点为基点，列出所有父节点直到祖先（找上级）。
面包屑导航数据（单条数据）

下面我以一幅图列出这三种形式（实线表现的是我们最终想要的数据，第三幅图中只有一条数据）：

OK，现在让我们来实现这几个需求，step by step。

1. 数据准备

根据上面的图中的数据创建表结构和测试数据

Region
Id Name 20ParentId RegionNameParentIdRegionNameParentId1RegionNameParentIdParentId2RegionNameParentIdParentIdParentId3RegionNameParentIdParentId5RegionNameParentId5<span style="color: blue">select <span style="color: gray">* <span style="color: blue">from Region

2. 正向递归实现

<pre class="code">/*

summary:递归获取所有子节点
*/
alter function GetRecursiveChildren
(
@Id int
)
returns @t table(Id int,ParentId int,[Level] int)
begin
declare @i int
set @i = 1
--根节点，Level = 0
insert into @t select @Id,@id,(select Name from Region where Id = @id),0
--直属子节点，Level = 1
insert into @t select Id,ParentId,Name,@i from Region where ParentId = @Id

--如果没有新的值插入，循环结束
while @@rowcount<>0
begin
set @i = @i + 1;
insert into @t
select
a.Id,a.ParentId,a.Name,@i
from
Region a,@t b
where
a.ParentId = b.Id and b.Level = @i - 1
end
return
end
go
--调用函数
select * from GetRecursiveChildren(2)

执行上面的函数得到如下图的结果：

-----------------------------------------------------------------------------------------------------------------------------

当然自sql 2005后微软提供了也可以用于递归查询，请参阅。

上面的递归用CTE的sql代码如下：

<pre class="code">declare @id int
set @id = 2
;with t as--如果CTE前面有语句，需要用分号隔断
(
select Id,Name
from Region
where Id = @id
union all
select r1.Id,r1.ParentId,r1.Name
from Region r1 join t as r2 on r1.ParentId = r2.Id
)
select * from t order by Id

3. 逆向递归实现

<pre class="code">create function GetRecursiveParent
(
@Id int
)
returns @t table(Id int,[Level] int)
as
begin
declare @i int
set @i = 1
--插入末节点，Level = 0
insert into @t select @Id,0
--插入末节点的父节点，Level = 1
insert into @t select Id,@i from Region
where Id = (select ParentId from Region where Id = @Id)
--如果没有新的值插入，循环结束
while @@rowcount<>0
begin
set @i = @i + 1;
insert into @t
select
a.Id,@t b
where
a.Id = b.ParentId and b.Level = @i - 1
end
return
end
go
--调用函数
select * from GetRecursiveParent(10)
go

执行这个函数得到的结果如下：

4. 面包屑实现

<pre class="code">create function GetLevel
(
@Id int
)
returns @level table(IdLevel varchar(100),NameLevel nvarchar(200))
as
begin
declare @IdLevel varchar(100),@NameLevel nvarchar(200),@Name nvarchar(50)
select @IdLevel = cast(@Id as varchar(10))
select @NameLevel = (select Name from Region where Id = @Id)

</span><span style="color: blue"&gt;while</span><span style="color: gray"&gt;(exists(</span><span style="color: blue"&gt;select </span>Id<span style="color: gray"&gt;,</span>ParentId <span style="color: blue"&gt;from </span>Region <span style="color: blue"&gt;where </span>Id <span style="color: gray"&gt;= (</span><span style="color: blue"&gt;select </span>ParentId <span style="color: blue"&gt;from </span>Region <span style="color: blue"&gt;where </span>Id <span style="color: gray"&gt;= </span>@Id<span style="color: gray"&gt;)))
</span><span style="color: blue"&gt;begin
    select </span>@Id <span style="color: gray"&gt;= </span>Id<span style="color: gray"&gt;,</span>@Name <span style="color: gray"&gt;= </span>Name <span style="color: blue"&gt;from </span>Region <span style="color: blue"&gt;where </span>Id <span style="color: gray"&gt;= (</span><span style="color: blue"&gt;select </span>ParentId <span style="color: blue"&gt;from </span>Region <span style="color: blue"&gt;where </span>Id <span style="color: gray"&gt;= </span>@Id<span style="color: gray"&gt;)
    </span><span style="color: blue"&gt;select </span>@IdLevel <span style="color: gray"&gt;= </span><span style="color: magenta"&gt;cast</span><span style="color: gray"&gt;(</span>@Id <span style="color: blue"&gt;as varchar</span><span style="color: gray"&gt;(</span>10<span style="color: gray"&gt;)) + </span><span style="color: red"&gt;'>' </span><span style="color: gray"&gt;+ </span>@IdLevel
    <span style="color: blue"&gt;select </span>@NameLevel <span style="color: gray"&gt;= </span>@Name <span style="color: gray"&gt;+ </span><span style="color: red"&gt;'>' </span><span style="color: gray"&gt;+ </span>@NameLevel
<span style="color: blue"&gt;end
insert into </span>@level <span style="color: blue"&gt;select </span>@IdLevel<span style="color: gray"&gt;,</span>@NameLevel
<span style="color: blue"&gt;return

end
go
--调用函数
select * from GetLevel(10)
go

调用这个函数的结果如下：

本文sql源代码下载：

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!