SQL 函數設計實戰：利用資料表變數替代臨時表實現複雜彙總邏輯 (GETALL 函數範例)

設計背景

需求：依起始/結束時間區間，統計各電解槽 (Electrobath_No) 的計劃產量、實際產量、實際出槽量、零產量計次、零出槽計次。邏輯涉及多表自我聯結去重、多維度彙總，適合封裝為**資料表值函數 (TVF)**。

完整函數程式碼 (Inline / Multi-statement TVF)

IF EXISTS (
    SELECT * FROM dbo.sysobjects 
    WHERE id = OBJECT_ID(N'[dbo].[GETALL]') 
      AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[GETALL];
GO

CREATE FUNCTION GETALL (
    @dateStart DATETIME,
    @dateEnd   DATETIME
)
RETURNS @Result TABLE (
    ENo           INT,
    PlanTotal     INT,
    FactTotal     INT,
    FactOutTotal  INT,
    FactZero      INT,
    FactOutZero   INT
)
AS
BEGIN
    -- 1. 計劃產量：去重邏輯 (同槽同日期同計數取最大狀態非0、實績非0)
    DECLARE @Plan TABLE (ENo INT, PlanTotal INT);
    INSERT @Plan
    SELECT a.Electrobath_No, SUM(a.Al_P_Plan) AS PlanTotal
    FROM Al_Product2005 a
    WHERE a.C_Date BETWEEN @dateStart AND @dateEnd
      AND a.Al_Status <> '0'
      AND a.Al_P_Fact <> '0'
      AND NOT EXISTS (
          SELECT 1 FROM Al_Product2005 b
          WHERE b.Electrobath_No = a.Electrobath_No
            AND b.C_Date = a.C_Date
            AND b.Al_P_Count = a.Al_P_Count
            AND b.Al_F_Count > a.Al_F_Count
            AND b.Al_Status <> 0
      )
    GROUP BY a.Electrobath_No;

    -- 2. 實際產量與出槽量彙總
    DECLARE @Fact TABLE (ENo INT, FactTotal INT, FactOutTotal INT);
    INSERT @Fact
    SELECT Electrobath_No, 
           SUM(Al_P_Fact) AS FactTotal,
           SUM(Al_P_FactOut) AS FactOutTotal
    FROM Al_Product2005
    WHERE C_Date BETWEEN @dateStart AND @dateEnd
    GROUP BY Electrobath_No;

    -- 3. 零產量計次
    DECLARE @FactZero TABLE (ENo INT, FactZero INT);
    INSERT @FactZero
    SELECT Electrobath_No, COUNT(*) AS FactZero
    FROM Al_Product2005
    WHERE C_Date BETWEEN @dateStart AND @dateEnd
      AND Al_P_Fact = '0'
    GROUP BY Electrobath_No;

    -- 4. 零出槽計次
    DECLARE @FactOutZero TABLE (ENo INT, FactOutZero INT);
    INSERT @FactOutZero
    SELECT Electrobath_No, COUNT(*) AS FactOutZero
    FROM Al_Product2005
    WHERE C_Date BETWEEN @dateStart AND @dateEnd
      AND Al_P_FactOut = '0'
    GROUP BY Electrobath_No;

    -- 5. 多表變數聯結輸出最終結果
    INSERT @Result
    SELECT 
        a.ENo, a.PlanTotal, b.FactTotal, b.FactOutTotal,
        c.FactZero, d.FactOutZero
    FROM @Plan a
    JOIN @Fact b ON a.ENo = b.ENo
    JOIN @FactZero c ON a.ENo = c.ENo
    JOIN @FactOutZero d ON a.ENo = d.ENo;

    RETURN;
END;
GO

關鍵技術點解析

1. 資料表變數 vs 臨時表

本函數資料量可控、邏輯自包含，**資料表變數是最佳選擇**。

2. NOT EXISTS 去重邏輯

AND NOT EXISTS (
    SELECT 1 FROM Al_Product2005 b
    WHERE b.Electrobath_No = a.Electrobath_No
      AND b.C_Date = a.C_Date
      AND b.Al_P_Count = a.Al_P_Count
      AND b.Al_F_Count > a.Al_F_Count
      AND b.Al_Status <> 0
)

同槽、同日期、同產品計數下，保留 Al_F_Count 最大且狀態非 0 的記錄，避免重複計算計劃量。

3. 字串比較陷阱修正建議

原碼中 Al_P_Fact = '0'、Al_Status <> '0' 將數值欄位當字串比較，建議：

• 確認欄位資料類型，若為數值型請移除引號：Al_P_Fact = 0
• 若確為字串型，建議加上 TRIM() 防止空白干擾

4. 函數呼叫方式

-- 單一結果集直接查詢
SELECT * FROM dbo.GETALL('2024-01-01', '2024-01-31');

-- 套用至報表/應用程式
SELECT ENo, PlanTotal, FactTotal, 
       CASE WHEN PlanTotal = 0 THEN 0 
            ELSE CAST(FactTotal AS FLOAT) / PlanTotal * 100 
       END AS AchievementRate
FROM dbo.GETALL(@StartDate, @EndDate);

效能優化建議

• 若 Al_Product2005 資料量大，確保 (Electrobath_No, C_Date, Al_P_Count, Al_F_Count) 有適當索引
• 考慮將去重邏輯改為 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY Al_F_Count DESC) = 1 (需 2005+)
• 頻繁呼叫時，評估改為預存程序 + 輸出參數或資料表值參數 (TVP)