您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL 查詢排除週末的 N 個工作日前日期：日曆表法與算法解析

編輯：MySQL 資料庫相關知識

商業日期運算：查詢「排除週末的 5 個工作日前」日期的標準解法

業務常需計算「T-5 個工作日」（排除週六、週日），單純 DATE_SUB(NOW(), INTERVAL 5 DAY) 無法滿足。本文介紹業界標準的「日曆表法」與純算法實現，並對比優劣。

核心挑戰

週末分布不固定：5 個工作日可能跨越 7、8、9 甚至更多自然日（含節假日）
純算法需處理起始日為週五/週六/週日的邊界情況
節假日需求擴展性：法定節假日、調休日需動態維護

方案一：日曆表法（推薦，企業級標準）

建表：

CREATE TABLE biz_calendar (
    cal_date DATE PRIMARY KEY,
    is_workday TINYINT(1) NOT NULL DEFAULT 1,  -- 1=工作日 0=非工作日
    day_of_week TINYINT(1) GENERATED ALWAYS AS (DAYOFWEEK(cal_date)) STORED,  -- 1=週日 ... 7=週六
    holiday_name VARCHAR(100) DEFAULT NULL
) ENGINE=InnoDB;

初始化資料：腳本生成未來 10 年日期，標記週六/週日 is_workday=0，人工維護節假日。

查詢「當前日期前 5 個工作日」：

SELECT MAX(cal_date) AS target_date
FROM biz_calendar
WHERE cal_date < CURDATE()
  AND is_workday = 1
  AND (
    SELECT COUNT(*) 
    FROM biz_calendar b2 
    WHERE b2.cal_date <= biz_calendar.cal_date 
      AND b2.cal_date < CURDATE() 
      AND b2.is_workday = 1
  ) <= 5;

或使用窗口函數：

SELECT cal_date
FROM (
    SELECT cal_date,
           ROW_NUMBER() OVER (ORDER BY cal_date DESC) AS rn
    FROM biz_calendar
    WHERE cal_date < CURDATE() AND is_workday = 1
) t
WHERE rn = 5;

方案二：純 SQL 算法（無需額外表，MySQL 8.0+）

邏輯：基於起始星期幾，計算需額外增加的週末天數。

SET @start_date = CURDATE();
SET @workdays = 5;

SELECT DATE_SUB(@start_date, INTERVAL 
    @workdays + 
    2 * FLOOR((@workdays + WEEKDAY(@start_date)) / 5) +  -- 完整週的週末
    CASE 
        WHEN WEEKDAY(@start_date) + (@workdays % 5) >= 5 THEN 2  -- 跨越週末
        ELSE 0 
    END
DAY) AS target_date;

驗證測試：

起始日	星期	T-5 工作日	自然日差
2024-01-15	週一	2024-01-08	7 天
2024-01-17	週三	2024-01-10	7 天
2024-01-19	週五	2024-01-12	7 天
2024-01-20	週六	2024-01-12	8 天
2024-01-21	週日	2024-01-12	9 天

方案三：儲存函數封裝（可復用）

CREATE FUNCTION `get_prev_workday`(p_start_date DATE, p_workdays INT)
RETURNS DATE
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_target DATE;
    SET v_target = DATE_SUB(p_start_date, INTERVAL 
        p_workdays + 
        2 * FLOOR((p_workdays + WEEKDAY(p_start_date)) / 5) +
        CASE WHEN WEEKDAY(p_start_date) + (p_workdays % 5) >= 5 THEN 2 ELSE 0 END
    DAY);
    RETURN v_target;
END;

-- 使用
SELECT get_prev_workday(CURDATE(), 5);

方案對比與選型建議

維度	日曆表法	純算法
節假日支援	原生支援，靈活維護	極難支援，需硬編碼或輔助表
效能	索引查找極快，窗口函數線性	純計算，無 IO，微秒級
維護成本	需定期擴充表、標記節假日	零維護，但邏輯固化難擴展
適用場景	金融、薪資、合規、含節假日業務	簡單報表、無節假日需求、臨時查詢

最佳實踐建議

核心業務務必用日曆表：合規性、可審計、節假日變更只需更新表不改代碼
預計算物化視圖：CREATE VIEW v_workday_offset AS SELECT cal_date, LAG(cal_date, 5) OVER (ORDER BY cal_date) AS prev_5_workday FROM biz_calendar WHERE is_workday=1;
應用層緩存：近期常用日期結果緩存至 Redis，減少 DB 查詢
時區一致性：資料庫、應用、日曆表統一時區（如 Asia/Taipei）

商業日期運算看似簡單，實則暗藏邊界陷阱。選對工具（日曆表），才能在業務演進中保持穩健。