SQL Server 多筆訂單依序核銷金額計算實戰 (CTE 遞迴解法)

業務場景：給定總可用金額 (如 1700)，依訂單編號順序逐筆核銷，餘額不足時該筆部分核銷，後續訂單核銷金額為 0。本文展示使用 CTE (Common Table Expression) 的集合式解法，避免游標/迴圈，效能與可讀性俱佳。

測試資料建立

CREATE TABLE #t (id INT, dingdan VARCHAR(20), sale MONEY);
INSERT INTO #t VALUES 
(1,'a',100),(2,'b',200),(3,'c',300),(4,'d',400),(5,'e',500),
(6,'f',600),(7,'g',700),(8,'h',800),(9,'i',900),(10,'j',1000);

核心邏輯：累加前綴和比較

1. 計算每筆訂單之前所有訂單金額總和 (curr_sale_sum)
2. 若 curr_sale_sum + 當筆金額 ≤ 總額度 → 全額核銷
3. 否則 → 核銷 總額度 - curr_sale_sum (剩餘額度)
4. 結果若為負數則修正為 0

完整查詢 (SQL Server 2005+ 相容)

;WITH x1 AS (
  SELECT 
    t1.id, t1.dingdan, t1.sale,
    (SELECT ISNULL(SUM(t2.sale),0) FROM #t t2 WHERE t2.id < t1.id) AS curr_sale_sum
  FROM #t t1
),
x2 AS (
  SELECT 
    id, dingdan, sale,
    CASE WHEN curr_sale_sum + sale <= 1700 THEN sale 
         ELSE 1700 - curr_sale_sum END AS new_sale
  FROM x1
)
SELECT 
  id AS 序號, dingdan AS 訂單, sale AS 訂單金額,
  CASE WHEN new_sale < 0 THEN 0 ELSE new_sale END AS 核銷金額
FROM x2;

執行結果

序號  訂單  訂單金額  核銷金額
1     a     100.00    100.00
2     b     200.00    200.00
3     c     300.00    300.00
4     d     400.00    400.00
5     e     500.00    300.00  -- 餘額 300，部分核銷
6     f     600.00    0.00    -- 餘額耗盡
7     g     700.00    0.00
...

進階優化：SQL Server 2012+ 窗口函數寫法

SELECT 
  id, dingdan, sale,
  CASE 
    WHEN SUM(sale) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) <= 1700 THEN sale
    WHEN SUM(sale) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) < 1700 
      THEN 1700 - SUM(sale) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    ELSE 0
  END AS 核銷金額
FROM #t;

注意事項

• 範例假設 id 連續無間隙；實務上若有刪除導致間隙，請先用 ROW_NUMBER() OVER (ORDER BY 排序欄位) 產生連續序號再套用邏輯。
• 金額欄位建議使用 DECIMAL(19,4) 或 MONEY，避免浮點誤差。
• 大量資料時，確保排序欄位有索引支援，CTE 子查詢的相關子查詢可改用窗口函數或自我 JOIN 優化。