一道SQL题...(关于树型构造的在关系表中的存储及其利用处理)[MSSQL防范]
本文“一道SQL题...(关于树型构造的在关系表中的存储及其利用处理)[MSSQL防范]”是由七道奇为您精心收集,来源于网络转载,文章版权归文章作者所有,本站不对其观点以及内容做任何评价,请读者自行判断,以下是其具体内容:
相关谈论衔接:
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
/*
办法一是"高手"的惯性思维把简单的问题搞复杂了,"太累"!
办法二是思绪简单清楚,不但是"菜鸟"首选,"高手"也应沉思!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
本文地址: | 与您的QQ/BBS好友分享! |