相關討論連接:
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
/*
另外還要說一下封裝的程度的問題,具體情況具體分析,
本題就不適合定義函數直接得到最終結果!
以上答案僅供參考!!
歡迎繼續參與討論!
*/
網站運營seo文章大全提供全面的站長運營經驗及seo技術!
