树结点拥有不定个孩子结点
deptid是结点ID,top是树根结点的id,
ref指向双亲结点ID
deepest是结点的最大深度
先说明一下deepest字段.
例如有一棵树:
A
/ \
B C
/ \
D E
A结点有两个分支,A分支的深度为2,C分支深度为1,所以A结点的最大深度deepest应该为2
现需要一个SQL语句,实现以下功能:
给出一棵树中的某几个结点,分别查询这些结点中,使该结点拥有最大深度的分枝个数
例如,以下是表中以deptid=s11为顶级部门的部门结构的记录
deptid top ref deepest
s11 s11 NULL 4
s21 s11 s11 0
s22 s11 s11 3
s23 s11 s11 1
s31 s11 s22 2
s32 s11 s23 0
s41 s11 s31 1
s42 s11 s31 1
s43 s11 s31 1
s51 s11 s41 0
s52 s11 s42 0
s53 s11 s43 0
其树结构图为:
S11
/ ¦ \
S21 S22 S23
¦ \
S31 S32
/ ¦ \
S41 S42 S43
/ ¦ \
S51 S52 S53
现在给出S41、S31、S22、S11这几个结点，查询每个结点使其拥有最大深度的分支个数
按这课树的结构，再解释下我要得到的是什么：
例如，S23的deepest为1，使S23拥有最大深度的分支只有S32，所以S23的brachcount为1
S31的deepest为2,使S31拥有最大深度的分支有S41，S42，S43三个，所以S31的branchcount为3
所以
我要的预期结果是：
deptid branchcount
S41 1
S31 2
S22 1
S11 1
请问这样的SQL语句应该怎样写呢？希望尽量是一条SQL语句，且查询的效率越高越好。
急啊，欢迎各位高手拔刀相助，非常感谢！！
网友回复:参考:
--查找指定节点的所有子节点的示例函数
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT> 0
BEGIN
SET @Level=@Level 1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
网友回复:
- SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --再参考 /*-- 第一层 - 1 第二层 - 2 3 第三层 - 4 5 6 7 第四层 - 8 9 10 11 12 13 14 15 将顺序的数据(1~N)依如下规则排列 1,排成塔形. 2,下一层所排数字是对上一层的两倍.(第一层为1个,第二层为2个,第三层4个,如此类推) 3,自上到下,自左到右紧密排列. 现在要找出每个号码下面号码的个数. --邹建 2004.4--*/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_id] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id_num]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_id_num] GO --得到每个号码下面包含的号码 create function f_id( @num int, --那个数下面的子 @max_num int --统计结束的数字 )returns @re table(id int identity(1,1),num int,level int) as begin declare @level int,@left int,@i int,@j int if @num>@max_num goto lb_re select @level=0,@i=@num while @i>1 select @level=@level 1,@i=@i/2 insert @re values(@num,@level 1) select @left=(@num%power(2,@level))*2 ,@level=@level 1 ,@num=power(2,@level) ,@i=0,@j=2 while 1=1 begin while @i<@j begin if (@left @num @i)>@max_num goto lb_re insert @re values(@left @num @i,@level 1) set @i=@i 1 end select @level=@level 1 ,@left=@left*2 ,@num=@num*2 ,@i=0,@j=@j*2 end lb_re: return end go --得到每个号码下面包含的号码个数 create function f_id_num( @num int, --那个数下面的子 @max_num int --统计结束的数字 )returns int as begin declare @level int,@left int,@re int,@i int,@j int set @re=0 if @num>@max_num goto lb_re select @level=0,@i=@num while @i>1 select @level=@level 1,@i=@i/2 select @left=(@num%power(2,@level))*2 ,@level=@level 1 ,@num=power(2,@level) ,@i=0,@j=2 while 1=1 begin while @i<@j begin if (@left @num @i)>@max_num goto lb_re select @re=@re 1,@i=@i 1 end select @level=@level 1 ,@left=@left*2 ,@num=@num*2 ,@i=0,@j=@j*2 end lb_re: return(@re) end go --调用实现查询 select * from f_id(2,8) select dbo.f_id_num(2,8) go /*--测试结果 id num level ----------- ----------- ----------- 1 3 2 2 6 3 3 7 3 4 12 4 5 13 4 6 14 4 7 15 4 （所影响的行数为 7 行） --*/
评论