UDF在層次型數據處理中的妙用之三

UDF在層次型數據處理中的妙用

三、計算合計數和子樹深度

　　現在來看幾個需要竅門才能解決的問題——如果不用遞歸就很難解決的問題。假定我們想要得到從指定管理員開始的特定子樹下所有雇員的薪水總額。Listing 4顯示了如何用dbo.ufn_GetSubtreeSalary函數來完成這個任務。


LISTING 4：計算子樹的合計數

CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@mgrid AS int
)
RETURNS int
AS
BEGIN

RETURN (SELECT Salary
FROM Employees WHERE empid = @mgrid) +
CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees
WHERE mgrid = @mgrid)
ELSE 0
END
END


　　注意dbo.ufn_GetSubtreeSalary函數非常簡短；雖然我們面臨的任務很復雜，但函數只包含一個RETURN語句。dbo.ufn_GetSubtreeSalary函數查詢指定管理員的薪水，再加上每一個管理員直接下屬所包含子樹的薪水總和。現在，試著用這個新的函數計算從Janet（empid是3）開始的子樹的薪水總額，答案將是20000：

SELECT dbo.ufn_GetSubtreeSalary(3)
　　用類似的方法可以計算子樹的深度，如Listing 5所示。dbo.ufn_GetSubtreeDepth函數也返回一個CASE表達式的結果，但兩者的代碼有所不同。


LISTING 5：計算子樹的深度

CREATE FUNCTION dbo.ufn_GetSubtreeDepth
(
@mgrid AS int
)
RETURNS int
AS
BEGIN

RETURN CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @mgrid)
THEN 1 + (SELECT MAX(dbo.ufn_GetSubtreeDepth(empid))
FROM Employees
WHERE mgrid = @mgrid)
WHEN EXISTS(SELECT * FROM Employees WHERE empid = @mgrid)
THEN 1
ELSE NULL
END
END


　　CASE表達式先檢查指定的管理員是否有下屬。如果有下屬，函數返回1加上該管理員直接下屬子樹的最大深度——因此，這裡出現了遞歸；如果指定的管理員沒有下屬，CASE表達式確定該管理員是否存在。如存在，則函數返回1（對於沒有下屬的管理員，他的深度是1）；如果該管理員不存在，則CASE表達式返回NULL。現在，試驗一下這個函數，提供Nancy的雇員ID（Nancy是最高級別的老板），計算整棵樹的深度。計算結果將是5，它表明整個組織結構分5層。


SELECT dbo.ufn_GetSubtreeDepth(1)

關鍵詞：