您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

從演算法到 SQL 實作：集合思維重構複雜業務規則的三大經典案例

編輯：關於SqlServer

前言

資料庫是**批量運算**引擎，而非逐列處理器。將複雜演算法轉化為高效 SQL 的核心在於：**拒絕游標/迴圈，擁抱集合運算、關聯式代數、窗口函數**。本文透過三大經典案例——**分級統計、週期性排程、累積扣款**——展示從程序式演算法到集合式 SQL 的重構全過程。

案例一：成績分級 — 從游標到查閱表再到 CASE/連接

業務規則

student_Score (sno, cno, score, level)，依分數區間寫入等級：

90~100 → A
80~89 → B
60~79 → C
0~59 → D

演算法演進

版本 1：游標逐列 (RBAR) — ❌ 反模式


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;

**缺點**：上萬次單獨 UPDATE、鎖定爭用、計畫快取汙染、執行時間隨資料線性增長。

版本 2：多次批次 UPDATE — ⚠️ 可行但冗餘


UPDATE student_Score SET level = 'D';  -- 全部先設 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 BETWEEN 90 AND 100;

**缺點**：掃描表 4 次，高分列被重複更新 4 次 (D→C→B→A)，產生大量寫入 I/O 與記錄檔。

版本 3：查閱表 + 單次 UPDATE (推薦) — ✅ 集合思維


-- 1. 建立分級查閱表 (小型、靜態、可索引)
CREATE TABLE ScoreLevel (
    StartScore INT PRIMARY KEY,
    EndScore INT,
    Level CHAR(1)
);
INSERT INTO ScoreLevel VALUES (0,59,'D'), (60,79,'C'), (80,89,'B'), (90,100,'A');

-- 2. 單次 UPDATE 完成所有分級
UPDATE s
SET s.level = sl.Level
FROM student_Score s
JOIN ScoreLevel sl ON s.score BETWEEN sl.StartScore AND sl.EndScore;

**優勢**：單次掃描、單次寫入、查閱表可維護 (業務調整只改表不改碼)、優化器可選擇 Hash/Loop Join。

版本 4：CASE 運算式 (最簡潔，無額外物件) — ✅ 現代最佳


UPDATE student_Score
SET level = CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 60 THEN 'C'
    ELSE 'D'
END;

**適用**：規則固定、不需外部維護、單一陳述式完成。

案例二：週期性獎金結算 — 模運算實現無硬編碼排程

業務規則

每日 00:00 執行結算作業
獎金發放：每週一、週四 (即第 1、4、8、11... 天)
系統啟動日為 Day 0，每日遞增計數器

演算法設計

觀察規律：

奇數次發放 (第 1、3、5... 次) 間隔 7 天
偶數次發放 (第 2、4、6... 次) 間隔 7 天
相鄰奇偶次間隔 3 天

統一數學模型：第 N 天發放 ⇔ N % 7 = 0 或 (N - 3) % 7 = 0

SQL 實作


-- 1. 計數器表 (單列)
CREATE TABLE Misc (Days INT NOT NULL DEFAULT 0);
INSERT INTO Misc VALUES (0);

-- 2. 每日作業步驟
BEGIN TRAN;
    -- 取得當前天數
    DECLARE @Days INT;
    SELECT @Days = Days FROM Misc;

    -- 判斷是否發放
    IF @Days % 7 = 0 OR (@Days - 3) % 7 = 0
    BEGIN
        -- 執行獎金結算邏輯 (略)
        PRINT '今日發放獎金';
    END

    -- 遞增計數器
    UPDATE Misc SET Days = Days + 1;
COMMIT;

**核心洞見**：將「週一、週四」這類**業務語義**轉化為**數學不變量 (模運算)**，消除硬編碼日期陣列、行事曆表查詢，實現零維護排程邏輯。

案例三：累積扣款與獎勵 — 複雜狀態機的集合式實現

業務規則

每 4000 元獎金扣除 400 元作為「重複消費基金」
每扣滿 400 元 → 獎勵 1 件產品 (記錄於 award_repeat)
單期扣款不足 400 元 → 累計至下期
範例：累計 3600 → 不扣；3700 → 扣 100；7700 → 扣 410 + 產生 1 次重複消費

資料結構

Member (MemberID, TotalAward, RepeatNum) — 累計總獎金、已產生重複消費次數
AwardDay (MemberID, DayAward, DayDeduction) — 當期獎金、當期扣款
AwardRepeat (MemberID, RepeatDate) — 重複消費記錄
Award (MemberID, DayAward, DayDeduction, NetAward) — 最終發放明細

集合式演算法 (單一批次處理所有會員)

步驟 1：累計總獎金


UPDATE m
SET m.TotalAward = m.TotalAward + ad.DayAward
FROM Member m
JOIN AwardDay ad ON m.MemberID = ad.MemberID;

步驟 2：計算本期應扣款並產生重複消費記錄 (核心難點)

邏輯：應扣款 = FLOOR(TotalAward / 4000) * 400 - RepeatNum * 400
但需處理「單期不足 400 累計」→ 實際上 TotalAward 已包含歷史累計，故公式成立。


-- 產生重複消費記錄 (每滿 4000 產生一筆)
INSERT INTO AwardRepeat (MemberID, RepeatDate)
SELECT m.MemberID, GETDATE()
FROM Member m
WHERE m.TotalAward >= (m.RepeatNum + 1) * 4000;

-- 更新重複消費次數
UPDATE m
SET m.RepeatNum = m.RepeatNum + 1
FROM Member m
WHERE m.TotalAward >= (m.RepeatNum + 1) * 4000;

**關鍵**：WHERE 條件精確捕捉「剛跨越 4000 倍數門檻」的會員，單次集合操作完成所有會員的判斷與更新。

步驟 3：計算本期實際扣款與淨發放


UPDATE ad
SET ad.DayDeduction = CASE
    WHEN m.TotalAward >= (m.RepeatNum + 1) * 4000 THEN 400  -- 本期觸發新門檻
    ELSE m.TotalAward - m.RepeatNum * 400  -- 累計未滿 400，全扣
END
FROM AwardDay ad
JOIN Member m ON ad.MemberID = m.MemberID;

-- 最終發放表
INSERT INTO Award (MemberID, DayAward, DayDeduction, NetAward)
SELECT MemberID, DayAward, DayDeduction, DayAward - DayDeduction
FROM AwardDay;