SRC:http://blog.joycode.com/mmkk/archive/2004/05/13/21428.aspx
树形结构是应用中常用的数据结构,最简单的设计类似:
id,name,parent_id
这种最简单的设计通常需要结合递归来最终形成树形UI,而且,对于要取得某一个节点的所有下级节点也不是很方便,
出于这种考虑,使用如下UDF来简化这种操作:
--取得树形结构中当前节点的所有下级节点
CREATE FUNCTION [dbo].[GetChildCategories] (@parent_id int)
RETURNS @work Table (num int IDENTITY(1,1),category_id int) AS
BEGIN
declare @childrenCount int,@currCategory_id int,@num int
set @num = 1
insert @work
select category_id
from Category_Classification
where parent_id = @parent_id
set @childrenCount = @@ROWCOUNT
while (@num <= @childrenCount)
Begin
select top 1 @currCategory_id = category_id
From @work
Where num = @num
insert @work
select category_id
From Category_Classification
where parent_id = @currCategory_id
set @childrenCount = @childrenCount + @@ROWCOUNT
set @num = @num + 1
End
Return
END
主要是将一个递归操作转化为一个单一的循环操作,就这几句代码也不知道该怎么写注释了,
未有大数据量测试.