[SQL] CTE - 累計薪資

在網路上看見這個問題，要求每個父節和其子節點的總和。

• 網友提供的圖示

直覺以前好像解過，但又有點陌生，把 Blog 文章找出來才發現之間的小差異，這篇 [Challenge] 計算各經理和其部屬薪資 需求是計算最上層父節點和其全部子節點薪資總和，但這個網路問題是要計算每個父節點和其子結點總和，不侷限在最上層的父節點。

• Sample Data

IF OBJECT_ID('Employ','U') IS NOT NULL
    BEGIN
        DROP TABLE Employ
    END
  
CREATE TABLE Employ
(
    EmpNO INT,
    EmpName VARCHAR(20),
    ManagerNO INT,
    Salary MONEY
)
  
INSERT INTO Employ(EmpNO, EmpName, ManagerNO, Salary)
SELECT 1, 'Terry', NULL, 3000 UNION ALL
SELECT 2, 'Sean' , NULL, 3000 UNION ALL
SELECT 3, 'Peter' , 1, 1880 UNION ALL
SELECT 4, 'Dan' , 3, 2010 UNION ALL
SELECT 5, 'Ray' , 2, 1550 UNION ALL
SELECT 6, 'Doris' , 1, 1300 UNION ALL
SELECT 8, 'Mike' ,6, 2010 UNION ALL
SELECT 7, 'Paul' ,4, 1400

利用 CTE 搭配使用者自訂函數

• 建立使用者自訂函數 udfCalSalary

傳入參數為 EmpNO，即為父節點，會從父節點，開始往下跑 CTE 找出該該父節點下全部的子節點，最後再把金額 SUM 起來，即為該父節點的累積薪資。

CREATE FUNCTION dbo.udfCalSalary
( 
    @EmpNO int
)
RETURNS money
AS
    BEGIN
        DECLARE @CalSalary AS money

        ;
        WITH CTE AS
        (
            SELECT 
                EmpNO , 
                ManagerNO , 
                Salary
            FROM Employ
            WHERE EmpNO = @EmpNO
            UNION ALL
            SELECT 
                E.EmpNO , 
                E.ManagerNO , 
                E.Salary
            FROM CTE AS T
                JOIN Employ AS E ON T.EmpNO = E.ManagerNO 
        )
        SELECT @CalSalary = SUM(Salary)
        FROM CTE

        RETURN @CalSalary
    END

• 利用 CTE 跑出整個部屬關係圖並利用 udfCalSalary 找出累計金額

; 
WITH CTE AS
(
    SELECT 
        EmpName , 
        EmpNO , 
        ManagerNO , 
        Salary , 
        0 AS lvl , 
        CAST(EmpName AS varchar(100)) AS TreePath
    FROM Employ
    WHERE ManagerNO IS NULL
    UNION ALL
    SELECT 
        E.EmpName , 
        E.EmpNO , 
        E.ManagerNO , 
        E.Salary , 
        lvl + 1 , 
        CAST(T.TreePath + '_' + E.EmpName AS varchar(100)) AS TreePath
    FROM CTE AS T
        JOIN Employ AS E ON T.EmpNO = E.ManagerNO
)
SELECT
    REPLICATE(SPACE(10),lvl) + EmpName AS EmpNameTree,
    Salary ,
    dbo.udfCalSalary(EmpNO) AS CalSalary
FROM CTE
ORDER BY TreePath

利用 CTE 搭配 APPLY

• 建立資料表值函數 SalaryStat

CREATE FUNCTION dbo.SalaryStat (@EmpNO char(5))
RETURNS TABLE
AS
RETURN
(
    WITH CTE AS
    (
        SELECT
            EmpNO , 
            ManagerNO , 
            Salary
        FROM Employ
        WHERE EmpNO = @EmpNO
        UNION ALL
        SELECT
            E.EmpNO , 
            E.ManagerNO , 
            E.Salary
        FROM CTE AS T
            JOIN Employ AS E ON T.EmpNO = E.ManagerNO 
    )
    SELECT SUM(Salary) AS CalSalary
    FROM CTE
)
GO 

• 利用 CTE 跑出整個部屬關係圖並利用 SalaryStat 找出累計金額

; 
WITH CTE AS
(
    SELECT
        EmpName , 
        EmpNO , 
        ManagerNO , 
        Salary , 
        0 AS lvl , 
        CAST(EmpName AS varchar(100)) AS TreePath
    FROM Employ
    WHERE ManagerNO IS NULL
    UNION ALL
    SELECT
        E.EmpName , 
        E.EmpNO , 
        E.ManagerNO , 
        E.Salary , 
        lvl + 1 , 
        CAST(T.TreePath + '_' + E.EmpName AS varchar(100)) AS TreePath
    FROM CTE AS T
        JOIN Employ AS E ON T.EmpNO = E.ManagerNO
)
SELECT
    REPLICATE(SPACE(10),T1.lvl) + T1.EmpName AS EmpNameTree,
    T1.Salary ,
    T2.CalSalary
FROM CTE AS T1
    CROSS APPLY dbo.SalaryStat (T1.EmpNO) AS T2
ORDER BY TreePath

上述兩種做法都可以得到下圖結果

延伸閱讀
• [Challenge] 計算各經理和其部屬薪資
• [SQL] APPLY
  

參考資料
• 問題出處
• 論壇問題出處