萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

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

函數極簡短,僅一個 RETURN 語句:取得指定管理員薪資,加上每位直屬下屬子樹薪資總和 (遞歸)。測試 Janet (empid=3) 的子樹薪資總額為 20000:

SELECT dbo.ufn_GetSubtreeSalary(3)

同理可計算子樹深度,Listing 5 所示。

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

邏輯:若有下屬,返回 1 + 直屬下屬子樹最大深度 (遞歸);若無下屬但存在,深度為 1;不存在返回 NULL。測試 Nancy (empid=1,最高層) 整棵樹深度為 5:

SELECT dbo.ufn_GetSubtreeDepth(1)
copyright © 萬盛學電腦網 all rights reserved