关于树型结构的在关系表中的存储及其应用处理


原题:
表:
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

/*
另外还要说一下封装的程度的问题,具体情况具体分析,
本题就不适合定义函数直接得到最终结果!
以上答案仅供参考!!

 

本文作者:



相关阅读:
Oracle DBA笔试及面试题汇总:基础篇
MySQL替代Oracle之后产生的技术支持问题
Ext.FormPanel 提交和 Ext.Ajax.request 异步提交函数的区别
表单设计器—HTML元素操作
在Linux环境下安装JSP
对于访问IIS元数据库失败的解决
第6课:学习更多元素
Windows7操作系统无法玩魔兽3的问题
不支持fsockopen但支持culr环境下下ucenter与modoer通讯问题
掌握XML实例演练ASP+XML编程
B/S开发中常用javaScript技术与代码
Oracle的dual虚拟表介绍
php引用地址改变变量值的问题
简洁的一个实现网页全屏代码
快速导航
PHP MySQL HTML CSS JavaScript MSSQL AJAX .NET JSP Linux Mac ASP 服务器 SQL jQuery C# C++ java Android IOS oracle MongoDB SQLite wamp 交通频道 作文范文 中考满分作文1700字:最佳中考满分作文--山西卷 梦幻之旅3作文500字 心中有她 拿破仑传读后感:荒野雄狮——读拿破仑有感 成都市某县农业发展局创先争优活动工作总结 打个电话给家人 局大院的秋色550字 蚊子大战 一个奇怪的人 校园之春400字 2012年保险公司经理工作总结及2013年工作计划 老师真了不起 思恋作文200字 中秋节搞笑祝福语精选 等车心情 春袭作文700字 感恩节促销活动方案 青春,不会有那么多如果 小老鼠皮皮的宝贝 当一回大人(刘春森) 月末 回来吧!美丽的地球!作文450字 爱,为你而免俗 一块抹布的独白 高中高一作文900字:爱似一首歌 在区委中心组会议的讲话 描写万圣节活动作文:万圣节活动丰富多彩 描写华山的诗句(现代诗) 躺在我胸前的夏天作文300字 高考作文之感(会员上传) 做一条河真好作文700字 这个世界,悲伤,每天都在泛滥。 2015员工素质培训演讲稿 先进农民入党申请书范文 如果我流泪了,你会心痛吗 幸福在手上 经典古代唯美句子 ”中华魂“伴我成长 平安夜给闺蜜的祝福语 为了爱情 伤了自己 人到中年好光景 我的一次发现作文 轻轻的爱你 25年同学聚会发言稿 学习《廉政准则》心得:用“四要”为《廉政准则》“转正”护航 离开,你是否有过心痛 妈妈再爱我一次观后感600字 局长先进性教育活动个人工作总结 《关键在于落实》读书心得 美人如玉作文600字

Copyright © 2016 phpStudy |