從演算法角度解析 SQL Server 集合運算與批次處理實戰

資料庫擅長集合基礎的批次運算，而非逐列游標處理。本文透過三個層次的演算法演進，展示如何將業務邏輯轉化為高效的 SQL 集合操作。

情境：學生成績分級

資料表 Student_Score(sno, cno, score, level)，需依分數更新 level：

• ≥90 → A
• 80-89 → B
• 60-79 → C
• <60 → D

方法一：游標逐列處理（反模式）

DECLARE cur CURSOR FOR SELECT sno, cno, score FROM Student_Score;
OPEN cur;
FETCH NEXT FROM cur INTO @sno, @cno, @score;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @score >= 90 SET @level = 'A';
    ELSE IF @score >= 80 SET @level = 'B';
    ELSE IF @score >= 60 SET @level = 'C';
    ELSE SET @level = 'D';
    UPDATE Student_Score SET level = @level WHERE sno=@sno AND cno=@cno;
    FETCH NEXT FROM cur INTO @sno, @cno, @score;
END
CLOSE cur; DEALLOCATE cur;

缺點：每列至少 2 次 I/O（讀+寫）、大量鎖定、執行緒序列化、效能極差。

方法二：多次批次 UPDATE（改進但仍有冗餘）

UPDATE Student_Score SET level = 'D';
UPDATE Student_Score SET level = 'C' WHERE score BETWEEN 60 AND 79;
UPDATE Student_Score SET level = 'B' WHERE score BETWEEN 80 AND 89;
UPDATE Student_Score SET level = 'A' WHERE score >= 90;

問題：分數 95 的列被更新 4 次（D→C→B→A），產生大量冗餘寫入與記錄檔。

方法三：單次 UPDATE 配合查閱表（最佳集合解法）

1. 建立對照表 Level_About(start_score, end_score, level)：

INSERT INTO Level_About VALUES (0, 59, 'D'), (60, 79, 'C'), (80, 89, 'B'), (90, 100, 'A');

2. 單一陳述式完成更新：

UPDATE Student_Score
SET level = la.level
FROM Level_About la
WHERE Student_Score.score BETWEEN la.start_score AND la.end_score;

優勢：單次掃描、單次寫入、業務規則外部化（修改對照表即可調整分級，無需改程式碼）、可擴充性強。

進階案例：複雜獎金扣除邏輯的集合化實作

業務規則：每 4000 元獎金扣 400 元為重複消費基金，滿 400 元贈商品，餘額結轉下期。

資料表設計：

• Member(total_award, repeat_num)：累計獎金、已扣除次數。
• Award_Day：每期獎金與扣除明細。
• Award_Repeat：重複消費記錄。

演算法步驟：

1. 計算本期獎金 → 累加至 Member.total_award。
2. 條件 F1: total_award >= (repeat_num + 1) * 4000。
3. 對滿足 F1 的會員：
    ① 寫入 Award_Repeat（PK, 日期）
    ② UPDATE Member SET repeat_num = repeat_num + 1 WHERE F1
    ③ 重複檢查 F1 直到無符合會員（WHILE 迴圈）。
4. 最後依 Award_Day 彙總實發獎金。

關鍵：用集合操作替代逐列邏輯，將「每人每期檢查」轉化為「全體會員集合篩選 + 批次更新」，即使迴圈執行，每輪也是全表集合運算，效能遠優於游標。

核心心法

1. 識別業務規則中的「分組/分類/閾值」邏輯 → 建立查閱表或 CTE。
2. 將逐列流程改寫為「篩選集合 → 批次操作」。
3. 善用 CASE、MERGE、窗格函數 處理複雜條件分支。
4. 必要時用 WHILE 控制迴圈，但迴圈體必須是集合陳述式。

隨著資料量爆增與業務複雜化，「演算法集合化」是資料庫開發從可用到高效的關鍵分水嶺。