SQL 實現按部門統計月卡餘額總額的查詢方案

需求背景

在一卡通消費系統中，經常需要統計各部門員工月卡的餘額總和。由於消費記錄分散在 T_ConsumeRec（消費明細）與 T_CashRec（儲值/取款明細）兩張表，且需取每位持卡人截至指定月份的最新一筆記錄餘額，再按部門彙總，邏輯較為複雜。

核心思路

1. 合併兩張流水表：用 UNION ALL 將消費與儲值記錄合併，統一欄位為 CustomerID, OpCount, OddFare, OpDt。
2. 取每卡最新操作序號：按 CustomerID 分組，找出 OpDt <= 目標月份 且 OpCount 最大的那一筆，即為該月結餘依據。
3. 關聯回流水表取餘額：用 CustomerID + OpCount 關聯回合併流水，拿到 OddFare（操作後餘額）。
4. 按部門彙總：T_Customers.Account 前 2 碼為一級部門代碼，與 T_Department.DpCode1 關聯，最後 GROUP BY DpCode1 求和。

完整 SQL（經測試可在 MS SQL Server 執行）

SELECT dp.dpname1 AS 部門,
       cust_dp_SumOddfre.sum_oddfare AS 當月卡總餘額
FROM (
    SELECT T_Department.DpCode1,
           SUM(custid_SumOddfare_group.sum_oddfare) AS sum_oddfare
    FROM (
        SELECT l2.CustomerID,
               SUM(r1.oddfare) AS sum_oddfare
        FROM (
            SELECT CustomerID, MAX(OpCount) AS max_opcount
            FROM (
                SELECT CustomerID, OpCount,
                       CAST(YEAR(OpDt) AS varchar) + '-' +
                       CAST(MONTH(OpDt) AS varchar) + '-01' AS dt
                FROM T_ConsumeRec
                UNION ALL
                SELECT CustomerID, OpCount,
                       CAST(YEAR(CashDt) AS varchar) + '-' +
                       CAST(MONTH(CashDt) AS varchar) + '-01' AS dt
                FROM T_CashRec
            ) l1
            WHERE dt <= '2005-06-01'  -- 可用參數傳遞
            GROUP BY CustomerID
        ) l2
        INNER JOIN (
            SELECT CustomerID, OpCount, OddFare
            FROM T_ConsumeRec
            UNION ALL
            SELECT CustomerID, OpCount, OddFare
            FROM T_CashRec
        ) r1 ON l2.CustomerID = r1.CustomerID
             AND r1.OpCount = l2.max_opcount
        GROUP BY l2.CustomerID
    ) custid_SumOddfare_group
    INNER JOIN T_Customers
        ON custid_SumOddfare_group.CustomerID = T_Customers.CustomerID
    INNER JOIN T_Department
        ON SUBSTRING(T_Customers.Account, 1, 2) = T_Department.DpCode1
       AND SUBSTRING(T_Customers.Account, 3, 2) = T_Department.DpCode2
       AND SUBSTRING(T_Customers.Account, 5, 3) = T_Department.DpCode3
    GROUP BY T_Department.DpCode1
) cust_dp_SumOddfre
INNER JOIN (
    SELECT DISTINCT DpCode1, DpName1
    FROM T_Department
) dp ON dp.DpCode1 = cust_dp_SumOddfre.DpCode1;

關鍵表結構參考

• T_ConsumeRec：消費明細，含 CustomerID, OpCount, OpDt, OddFare 等。
• T_CashRec：出納明細，含 CustomerID, OpCount, CashDt, OddFare 等。
• T_Customers：客戶主檔，Account 編碼規則為 DpCode1(2) + DpCode2(2) + DpCode3(3)。
• T_Department：三級部門表，主鍵 (DpCode1, DpCode2, DpCode3)。

優化建議

• 若資料量大，建議在 T_ConsumeRec、T_CashRec 上建立 (CustomerID, OpDt, OpCount) 覆蓋索引。
• 可將「取最新 OpCount」邏輯封裝為內嵌表值函數，供多處複用。
• 目標月份改用 @TargetMonth DATE 參數，避免字串拼接注入風險。