一道 SQL 题 ... (关于树型结构的在关系表中的存储及其应用处理)

原创 2003年03月06日 09:22:00

相关讨论连接:
http://expert.csdn.net/Expert/TopicView1.asp?id=1477009
原题:
表:
Tree (ID [Integer],ParentID [Integer],Remark [varchar])

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,2

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 4


参考 Tree 表中的父子关系,"祖先"的记录数要包括所有"后代"的记录数,统计 T 表中 F1 各个取值的记录数
ID      Counts
1       15
2       10
3       2
4       8
5       4
6       2
7       2


答案及简单分析:

/*
看了前几个人的答案,似乎都把问题想复杂了"游标"、"临时表"、"递归"。
"游标"、"临时表" 完全可以不用!
"递归" 思想当然应是解决树型结构的该想到的方法!
但是 T-SQL 的嵌套层次最多只能到 32!
icevi(按钮工厂) 的建议是非常值得提倡的,尽管 ID,ParentID 对于仅存储是足够经济的,
但是若用其提供表现形式,性能的确不会太好!
许多高效的树型结构论坛也确实是存储并维护各个节点的层次信息的数据,这样
显示起来仅需一条 SQL 即可!
下面是我的参考答案,两个自定义函数功能几乎一样,都是运算出前面所提的,
应最好主动维护的"层次信息":

方法一: UDF 递归实现! 有 32 层嵌套限制
*/

alter  FUNCTION dbo.Get32Ancestors
(@X integer)
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE @ID integer
DECLARE @ReturnValue VARCHAR(250)

SELECT TOP 1 @ID = ParentID
FROM tree
WHERE [id] = @X

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

RETURN @ReturnValue
END

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

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

*/

alter function GetAllAncestors (@X integer)
returns varchar(1000)
as
begin
declare @ReturnValue  varchar(1000)
declare @ID integer
declare @ParentID integer

set @ID = -1

select top 1 @ID=isnull([ID],0),@ParentID = isnull([ParentID],0)
from tree
where ID = @X

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
  end

set @ReturnValue = '-' + @ReturnValue

if @id>0
   set @ReturnValue = cast(@id as varchar) + isnull(@ReturnValue,'')

if @parentid =0 or  @id = @parentid
   set @ReturnValue = '0-'  + isnull(@ReturnValue,'')  

return(@ReturnValue)
--select dbo.GetAllAncestors(10)
end


go

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

若是本题分为两问:
1.求各节点层次信息
2.求属各节点含后代的记录数

可能大家就会受到一些启发!
函数定义完,下面就应该和 icevi(按钮工厂) 同志的答案异曲同工、不谋而和了
*/

select id,dbo.GetAllAncestors(id)
       ,(select count(*)
           from T
          where '-' + dbo.GetAllAncestors(f1) + '-' like '%-' + cast(tree.id as varchar) + '-%')
from tree

select id,dbo.Get32Ancestors(id)
       ,(select count(*)
           from T
          where '-' + dbo.Get32Ancestors(f1) + '-' like '%-' + cast(tree.id as varchar) + '-%')
from tree

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

一道SQL题...(关于树型结构的在关系表中的存储及其应用处理)

  • zgqtxwd
  • zgqtxwd
  • 2008年04月28日 12:21
  • 113

树状数组BITLA 4329

我去,数组最大值 跟 数据最大值 没搞清楚,WA了好多次都看不出来错在哪。这种错误一旦犯了,小数据根本看不不出来。。 #include #include #include #include...
  • u011526463
  • u011526463
  • 2013年10月02日 11:43
  • 389

Sql表间关系和连接查询应用举例

以前学连接查询时看的书上举的例子都是没有实际意义的,随便凑上两个表格,只是了解的语法,但却不明白到底为什么要用连接查询,连接查询的好处是什么。最近在学Hibernate,重温sql时想到一个特别好的案...
  • momowuwenderen
  • momowuwenderen
  • 2013年07月31日 12:19
  • 1378

LA4329 乒乓 树状数组

题意:训练指南上的例题。大致求a
  • u010454438
  • u010454438
  • 2014年09月06日 17:06
  • 366

LA4328 Ping Pong (树状数组)

题目:http://acm.hust.edu.cn/vjudge/problem/viewProblem.action?id=13895 题意:一条大街上住着n个乒乓求爱好者,经常组织比赛切磋技术。每...
  • w20810
  • w20810
  • 2015年07月07日 19:09
  • 362

LA4329(树状数组)

#include #include #include using namespace std; const int maxn=100005; int a[maxn],c[maxn],mini1[max...
  • shiqining888
  • shiqining888
  • 2013年12月14日 23:32
  • 364

树状数组uva1428

思路同poj1990,不多说。 代码如下: #include #include #include #include using namespace std; typedef long long L...
  • u010660276
  • u010660276
  • 2014年02月19日 20:11
  • 377

POJ 3928 Ping pong(树状数组基础题)

表示期末考试终于渡过了
  • gaoxiang36999
  • gaoxiang36999
  • 2014年07月24日 00:27
  • 367

ZOJ2492 ping pong(树状数组BIT)

C. Ping pong Time Limit: 1000msMemory Limit: 32768KB 64-bit integer IO format: %lld Java class...
  • zyd8888102
  • zyd8888102
  • 2016年04月30日 17:22
  • 189

poj3928 Ping pong(树状数组)

http://poj.org/problem?id=3928 题意:有n个乒乓球选手,住在一条直线上,从左到右依次输入。每个选手有一个技术等级值。他们想打比赛,就要找裁判,为了省时和公正,选裁判的条...
  • Flynn_curry
  • Flynn_curry
  • 2017年03月02日 21:25
  • 178
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:一道 SQL 题 ... (关于树型结构的在关系表中的存储及其应用处理)
举报原因:
原因补充:

(最多只允许输入30个字)