相關討論連接:
http://expert.csdn.Net/Expert/TopicVIEw1.ASP?id=1477009
原題:
表:
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
/*
另外還要說一下封裝的程度的問題,具體情況具體分析,
本題就不適合定義函數直接得到最終結果!
以上答案僅供參考!!
歡迎繼續參與討論!
*/China It Power . Comx3owK
