sql-函数实现三种父子递归.docxVIP

HYPERLINK /keepfool/archive/2012/02/05/2338700.html sql 函数实现三种父子递归 2012-02-05 00:31 by keepfool, 1164 visits, HYPERLINK /keepfool/archive/2012/02/05/2338700.html 收藏, HYPERLINK /keepfool/admin/EditPosts.aspx?postid=2338700 编辑 在实际运用中经常会创建这样的结构表Category(Id, ParentId, Name)，特别是用于树形结构时（菜单树，权限树..），这种表设计自然而然地会用到递归，若是在程序中进行递归（虽然在程序中递归真的更方便一些），无论是通过ADO.NET简单sql查找还是ORM属性关联都会执行多次sql语句，难免会造成一些性能上的损耗，所以干脆使用sql的函数来解决这个问题，用函数返回我们最终需要的结果。 针对这类需求，这里我列出三种常用的递归： 以一个节点为基点，列出所有子节点直到无子 （找下级） 。这有点儿像点兵点将，主帅只有一个，下面是左将、右将，左将下面又有千夫长、百夫长，点兵时主帅下令集合，下面的将军只管各自的队伍。 以一个节点为基点，列出所有父节点直到祖先（找上级） 。 面包屑导航数据（单条数据） 下面我以一幅图列出这三种形式（实线表现的是我们最终想要的数据，第三幅图中只有一条数据）： OK，现在让我们来实现这几个需求，step by step。 1. 数据准备 根据上面的图中的数据创建表结构和测试数据 create table Region ( Id int identity primary key, Name nvarchar(20), ParentId int ) go insert into Region(Name,ParentId) values(广东,NULL) insert into Region(Name,ParentId) values(深圳,1) insert into Region(Name,ParentId) values(惠州,1) insert into Region(Name,ParentId) values(罗湖区,2) insert into Region(Name,ParentId) values(福田区,2) insert into Region(Name,ParentId) values(龙岗区,2) insert into Region(Name,ParentId) values(惠阳区,3) insert into Region(Name,ParentId) values(龙门县,3) insert into Region(Name,ParentId) values(华强北,5) insert into Region(Name,ParentId) values(体育馆,5) select * from Region 2. 正向递归实现 /* * summary:递归获取所有子节点 */ alter function GetRecursiveChildren ( @Id int ) returns @t table(Id int,ParentId int,Name nvarchar(20), [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 @@rowcount0 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 GetRecursive