http://expert.csdn.net/Expert/topic/1375/1375432.xml?temp=.1278345-----------select
len(dbo.FN_32getsubclass(tc_id,default,default))-len(replace(dbo.FN_32getsubclass(tc_id,default,default),',',''))+1 as counts
,* from treeclass
len(dbo.FN_32getsubclass(tc_id,default,default))-len(replace(dbo.FN_32getsubclass(tc_id,default,default),',',''))+1 as counts
,* from treeclass
CREATE FUNCTION howmany (@f int,@s int)
RETURNS int AS
BEGIN declare @id int , @pid intDECLARE TreeC CURSOR local FOR
SELECT id,parentid
FROM Tree
where parentid=@fOPEN TreeC
FETCH NEXT FROM TreeC
INTO @ID,@PID
WHILE @@FETCH_STATUS = 0
begin
set @s=@s+1
set @s=dbo.howmany(@id,@s)FETCH NEXT FROM TreeC
INTO @ID,@PIDendCLOSE TreeC
DEALLOCATE TreeC
return @s
END
--- then execselect f1, dbo.howmany(f1,0) as qty from
(select distinct f1 from T) A
create table Tree (ID [Integer],ParentID [Integer],Re [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,2create table T(F1 int)
INSERT INTO T (F1)
SELECT 1 union all
SELECT 2
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---- exec
select f1, dbo.howmany(f1,0) as qty from
(select distinct f1 from T) A
---- exec result
ID qty
1 6
2 4
3 0
4 2
5 1
6 0
7 0
select A.*, isnull(B.cnt,0) as cnt into #temp1 from tree as A left join (select f1, count(*) as cnt from T group by f1) as B on A.id = B.f1
declare @i int
declare @cnt int
select @i = max(id) from #temp1
while @i > 0
begin
select @cnt=cnt from #temp1 where id = @i
update #temp1 set cnt = cnt + @cnt where id in (select parentid from #temp1 where id = @i)
set @i = @i - 1
end
select id, cnt from #temp1id cnt
----------- -----------
1 15
2 10
3 2
4 8
5 4
6 2
7 2(7 row(s) affected)
临时表 游标 还是免了吧!
id parentid level interid
1 0 1 1
2 1 2 1.2
3 1 2 1.3
4 2 3 1.2.4
5 4 4 1.2.4.5
6 5 5 1.2.4.5.6
7 2 3 1.2.7
再以这种结构去做查询，语句简单很多，效率也会高些。这样的表结构也不难维护。
如果这我上面写的这样的结构，语句就很简单了。
id parentid level interid
1 0 1 1
2 1 2 1.2
3 1 2 1.3
4 2 3 1.2.4
5 4 4 1.2.4.5
6 5 5 1.2.4.5.6
7 2 3 1.2.7
再以这种结构去做查询，语句简单很多，效率也会高些。这样的表结构也不难维护。
如果把表做成我上面写的这样的结构，语句就很简单了。
select id,
(select count(*)
from t t2 join tree t1 on t2.f1=t1.id
where left(t1.interid,len(t0.interid))=t0.interid
)
from tree t0
就可以搞定。
returns int
as
begin
declare @sum int
declare @a table(id int)
insert @a select @idwhile exists(
select 1
from @a a,tree b
where a.id=b.parentid
and b.id not in (select id from @a))insert @a
select b.id
from @a a,tree b
where a.id=b.parentid
and b.id not in (select id from @a)select @sum=count(*) from @a a,T b where a.id=b.f1
return @sum
end
select id,dbo.getSum(id) from tree
看了前几个人的答案,似乎都把问题想复杂了"游标"、"临时表"、"递归"。
"游标"、"临时表" 完全可以不用!
"递归" 思想当然应是解决树型结构的该想到的方法!
但是 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=@XIF @ID <> @X
BEGIN
SELECT @ReturnValue = cast(ISNULL(dbo.Get32Ancestors(@ID),'') as varchar) + '-'+ cast(@X as varchar)
END
ELSE SET @ReturnValue = @IDRETURN @ReturnValue
ENDgo
/*
方法二: 无任何限制,若层次太深,效率当然不会高(好像也没更好的办法)
*/
alter function GetAllAncestors (@X integer)
returns varchar(1000)
as
begin
declare @ReturnValue varchar(1000)
declare @ID integer
declare @ParentID integer select top 1 @ID=isnull([ID],0),@ParentID = isnull([ParentID],0)
from tree
where ID = @Xwhile @id <> @parentid
begin
if len(ltrim(rtrim(isnull(@ReturnValue,'')))) >0
set @ReturnValue = '-' +isnull(@ReturnValue,'')
set @ReturnValue= cast(@id as varchar) + isnull(@ReturnValue,'')
select top 1 @ID=isnull([ID],0),@ParentID = isnull([ParentID],0)
from tree
where ID = @parentid
if @parentid = 0
break
endreturn(cast(@id as varchar) + '-' + @ReturnValue)
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 treeselect id,dbo.Get32Ancestors(id)
,(select count(*)
from T
where '-' + dbo.Get32Ancestors(f1) + '-' like '%-' + cast(tree.id as varchar) + '-%')
from tree/*
另外还要说一下封装的程度的问题,具体情况具体分析,
本题就不适合定义函数直接得到最终结果!
以上答案仅供参考!!
欢迎继续参与讨论!
*/
insert @ values (4)while exists(select 1 from @ a,tree b where a.id=b.parentid and not exists (select 1 from @ where id=b.id))
insert @ select b.id from @ a,tree b where a.id=b.parentid and not exists (select id from @ where id=b.id)select * from @个人认为是树的最佳解法！
T表记录是无尽的，可能几百万或上千万。倒推的方法最后根动不了。
方法二: 无任何限制,若层次太深,效率当然不会高(好像也没更好的办法)
改进了一下:
1.断码不会死循环
2.GetAllAncestors(不存在的节点)返回NULL
3.GetAllAncestors(根节点)返回自己
*/
alter function GetAllAncestors (@X integer)
returns varchar(1000)
as
begin
declare @ReturnValue varchar(1000)
declare @ID integer
declare @ParentID integer set @ID = -1select top 1 @ID=isnull([ID],0),@ParentID = isnull([ParentID],0)
from tree
where ID = @Xwhile @id <> @parentid and @parentid <> 0 and @ID >0
begin
if @ReturnValue is not null
set @ReturnValue = '-' + @ReturnValue
set @ReturnValue= cast(@id as varchar) + isnull(@ReturnValue,'')
set @id = -1
--set @parentid = -2
select top 1 @ID=isnull([ID],0),@ParentID = isnull([ParentID],0)
from tree
where ID = @parentid
endset @ReturnValue = '-' + @ReturnValueif @ID > 0
set @ReturnValue = cast(@id as varchar) + isnull(@ReturnValue,'')
return(@ReturnValue)
--select dbo.GetAllAncestors(500)
end
一道 SQL 题 ... (关于树型结构的在关系表中的存储及其应用处理)
http://www.csdn.net/Develop/read_article.asp?id=17247欢迎捧场"点击"!
我还是坚持原来的看法且做了测试：
-----***环境***---
delete t
delete tree
declare @number int,@v int
set @number=0
while @number<1000
begin
set @v=cast(rand(checksum(newid()))*5 as int)+1
while @v>0
begin
insert t values(@number+1)
set @v=@v-1
end
insert tree values(@number+1,@number,null)
set @number=@number+1
end
--------------------------------------------
--------****所用三个方法**--------------
create function GetSum(@id int)
returns int
as
begindeclare @ table(id int)
declare @sum int
insert @ values(@id)while exists(select 1 from @ a,tree b where a.id=b.parentid and not exists (select 1 from @ where id=b.id))
insert @ select b.id from @ a,tree b where a.id=b.parentid and not exists (select 1 from @ where id=b.id)
select @sum=count(*) from T where exists (select 1 from @ where id=T.F1)
return @sum
end
go
----调用：
select *,dbo.GetSum(id) from tree
-------------------------------------------------
CREATE function GetAllAncestors (@X integer)
returns varchar(1000)
as
begin
declare @ReturnValue varchar(1000)
declare @ID integer
declare @ParentID integer set @ID = -1select top 1 @ID=isnull([ID],0),@ParentID = isnull([ParentID],0)
from tree
where ID = @Xwhile @id <> @parentid and @parentid <> 0 and @ID >0
begin
if @ReturnValue is not null
set @ReturnValue = '-' + @ReturnValue
set @ReturnValue= cast(@id as varchar) + isnull(@ReturnValue,'')
set @id = -1
--set @parentid = -2
select top 1 @ID=isnull([ID],0),@ParentID = isnull([ParentID],0)
from tree
where ID = @parentid
endset @ReturnValue = '-' + @ReturnValueif @ID > 0
set @ReturnValue = cast(@id as varchar) + isnull(@ReturnValue,'')
return(@ReturnValue)
--select dbo.GetAllAncestors(500)
end
-------调用：
select id,(select count(*) from t t2 join tree t1 on t2.f1=t1.id where left(dbo.GetAllAncestors(t1.id),len(dbo.GetAllAncestors(t0.id)))=dbo.GetAllAncestors(t0.id)) from tree t0
---------------------------------------------------------------
if object_id('tempdb..#temp1') is not null drop table #temp1
select A.*,isnull(B.cnt,0) cnt into #temp1 from tree as A left join (select f1,count(*) cnt from T group by f1) as B on A.id = B.f1
declare @i int
declare @cnt int
select @i=max(id) from #temp1
while @i>0
begin
select @cnt=cnt from #temp1 where id = @i
update #temp1 set cnt = cnt + @cnt where id in (select parentid from #temp1 where id = @i)
set @i=@i-1
end
select id, cnt from #temp1
--------------------------------------------------------
--****P3 800 512M win2kas sql2k企业 下结果****----
-------------------getsum---------GetAllAncestors--------临时表
--tree有50条记录： 0:00:04 0:00:13 0:00:00
--tree有60条记录： 0:00:06 0:00:22 0:00:00
--tree有70条记录： 0:00:10 0:00:38 0:00:00
--tree有70条记录： 0:00:14 0:01:00 0:00:00
--tree有1000条记录：不敢测 不敢测 0:00:03
主要的问题是：
实际中，TREE表记录有限不多，而T表记录很多，可能是TREE表的上百倍。
所以他的方法，函数运不仅要在TREE表运行，在T表也要。
相当慢（我测试过）。
优点是比起临时表来，内存用的要少点。适合于记录量很少的情况用。
方法二: 无任何限制,若层次太深,效率当然不会高(好像也没更好的办法)
改进了一下:
1.正常节点均从0显示! 0-1-32.断码 显示 -7-8-9-10
3.GetAllAncestors(不存在的节点)返回NULL
4.GetAllAncestors(根节点)返回 0-自己
5.死循环点显示: 4-5-6-4-8alter function GetAllAncestors (@X integer)
returns varchar(1000)
as
begin
declare @ReturnValue varchar(1000)
declare @ID integer
declare @ParentID integer set @ID = -1select top 1 @ID=isnull([ID],0),@ParentID = isnull([ParentID],0)
from tree
where ID = @Xwhile @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
endset @ReturnValue = '-' + @ReturnValueif @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
Got it~~~