相关讨论连接:http://expert.csdn.net/Expert/TopicView1.asp?id=1477009原题:表:Tree (ID [Integer],ParentID [Integer],Remark [varchar])China It Power . Comx3owK
INSERT INTO Tree (ID,ParentID) SELECT 1,0 UNION ALL SELECT 2,1 UNION ALL SELECT 3,1 UNION ALL SELECT 4,2 UNION ALL SELECT 5,4 UNION ALL SELECT 6,5 UNION ALL SELECT 7,2China It Power . Comx3owK
T(F1,......) INSERT INTO T (F1) SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 1 UNION ALL SELECT 7 UNION ALL SELECT 6 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 1 UNION ALL SELECT 7 UNION ALL SELECT 6 UNION ALL SELECT 4China It Power . Comx3owK
参考 Tree 表中的父子关系,"祖先"的记录数要包括所有"后代"的记录数,统计 T 表中 F1 各个取值的记录数?ID Counts1 152 103 24 85 46 27 2China It Power . Comx3owK
答案及简单分析:China It Power . Comx3owK
/*看了前几个人的答案,似乎都把问题想复杂了"游标"、"临时表"、"递归"。"游标"、"临时表" 完全可以不用!"递归" 思想当然应是解决树型结构的该想到的方法!但是 T-SQL 的嵌套层次最多只能到 32!icevi(按钮工厂) 的建议是非常值得提倡的,尽管 ID,ParentID 对于仅存储是足够经济的,但是若用其提供表现形式,性能的确不会太好!许多高效的树型结构论坛也确实是存储并维护各个节点的层次信息的数据,这样显示起来仅需一条 SQL 即可!下面是我的参考答案,两个自定义函数功能几乎一样,都是运算出前面所提的,应最好主动维护的"层次信息":China It Power . Comx3owK
方法一: UDF 递归实现! 有 32 层嵌套限制*/China It Power . Comx3owK
alter FUNCTION dbo.Get32Ancestors(@X integer)RETURNS VARCHAR(250)ASBEGINDECLARE @ID integerDECLARE @ReturnValue VARCHAR(250)China It Power . Comx3owK
SELECT TOP 1 @ID = ParentIDFROM tree WHERE [id] = @XChina It Power . Comx3owK
IF @ID <> @X BEGIN SELECT @ReturnValue = cast(ISNULL(dbo.Get32Ancestors(@ID),') as varchar) '-' cast(@X as varchar) ENDELSE SET @ReturnValue = @IDChina It Power . Comx3owK
RETURN @ReturnValueENDChina It Power . Comx3owK
go/*2003-3-5方法二: 无任何限制,若层次太深,效率当然不会高(好像也没更好的办法)改进了一下:1.正常节点均从0显示! 0-1-3China It Power . Comx3owK
2.断码 显示 -7-8-9-103.GetAllAncestors(不存在的节点)返回NULL4.GetAllAncestors(根节点)返回 0-自己 5.死循环点显示: 4-5-6-4-8China It Power . Comx3owK
*/China It Power . Comx3owK
alter function GetAllAncestors (@X integer)returns varchar(1000)asbegindeclare @ReturnValue varchar(1000)declare @ID integerdeclare @ParentID integer China It Power . Comx3owK
set @ID = -1China It Power . Comx3owK
select top 1 @ID=isnull([ID],0),@ParentID = isnull([ParentID],0)from treewhere ID = @XChina It Power . Comx3owK
while @id <> @parentid and @parentid <> 0 and @ID >0 and '-' isnull(@ReturnValue,') '-' not like '%-' cast(@id as varchar) '-%' begin if @ReturnValue is not null set @ReturnValue = '-' @ReturnValue set @ReturnValue= cast(@id as varchar) isnull(@ReturnValue,') set @id = -1 select top 1 @ID=isnull([ID],0),@ParentID = isnull([ParentID],0) from tree where ID = @parentid endChina It Power . Comx3owK
set @ReturnValue = '-' @ReturnValueChina It Power . Comx3owK
if @id>0 set @ReturnValue = cast(@id as varchar) isnull(@ReturnValue,')China It Power . Comx3owK
if @parentid =0 or @id = @parentid set @ReturnValue = '0-' isnull(@ReturnValue,') China It Power . Comx3owK
return(@ReturnValue)--select dbo.GetAllAncestors(10)endChina It Power . Comx3owK
goChina It Power . Comx3owK
/*方法一是"高手"的惯性思维把简单的问题搞复杂了,"太累"!方法二是思路简单清晰,不但是"菜鸟"首选,"高手"也应反思!China It Power . Comx3owK
若是本题分为两问:1.求各节点层次信息2.求属各节点含后代的记录数China It Power . Comx3owK
可能大家就会受到一些启发!函数定义完,下面就应该和 icevi(按钮工厂) 同志的答案异曲同工、不谋而和了 */China It Power . Comx3owK
select id,dbo.GetAllAncestors(id) ,(select count(*) from T where '-' dbo.GetAllAncestors(f1) '-' like '%-' cast(tree.id as varchar) '-%') from treeChina It Power . Comx3owK
select id,dbo.Get32Ancestors(id) ,(select count(*) from T where '-' dbo.Get32Ancestors(f1) '-' like '%-' cast(tree.id as varchar) '-%') from treeChina It Power . Comx3owK
/* 另外还要说一下封装的程度的问题,具体情况具体分析,本题就不适合定义函数直接得到最终结果!以上答案仅供参考!!欢迎继续参与讨论!*/China It Power . Comx3owK