一道SQL题...(关于树型结构的在关系表中的存储…

相关讨论连接:
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 Counts
1 15
2 10
3 2
4 8
5 4
6 2
7 2
China 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)
AS
BEGIN
DECLARE @ID integer
DECLARE @ReturnValue VARCHAR(250)China It Power . Comx3owK

SELECT TOP 1 @ID = ParentID
FROM tree
WHERE [id] = @XChina It Power . Comx3owK

IF @ID <> @X
BEGIN
SELECT @ReturnValue = cast(ISNULL(dbo.Get32Ancestors(@ID),') as varchar) '-' cast(@X as varchar)
END
ELSE SET @ReturnValue = @IDChina It Power . Comx3owK

RETURN @ReturnValue
ENDChina It Power . Comx3owK

go
/*
2003-3-5
方法二: 无任何限制,若层次太深,效率当然不会高(似乎也没更好的办法)
改进了一下:
1.正常节点均从0显示! 0-1-3China It Power . Comx3owK

2.断码 显示 -7-8-9-10
3.GetAllAncestors(不存在的节点)返回NULL
4.GetAllAncestors(根节点)返回 0-自己
5.死循环点显示: 4-5-6-4-8China It Power . Comx3owK

*/China It Power . Comx3owK

alter function GetAllAncestors (@X integer)
returns varchar(1000)
as
begin
declare @ReturnValue varchar(1000)
declare @ID integer
declare @ParentID integer China It Power . Comx3owK

set @ID = -1China It Power . Comx3owK

select top 1 @ID=isnull([ID],0),@ParentID = isnull([ParentID],0)
from tree
where 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)
end
China It Power . Comx3owK

goChina It Power . Comx3owK

/*
方法一是"高手"的惯性思维把简单的问题搞复杂了,"太累"!
方法二是思路简单清楚,不但是"菜鸟"最好选择,"高手"也应反思!

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有