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)