SQL 多表 UNION ALL 合併查詢：學生違紀記錄高效按時間區間彙總

四張違紀記錄表（到課率、早鍛煉、衛生、晚歸）結構相似但字段不完全一致，需按時間區間高效彙總所有學生的違紀信息。

標準 UNION ALL 方案

SELECT 
    s.*, 
    v.check_time, 
    v.violation_type, 
    v.count, 
    v.class_hours, 
    v.dorm_no, 
    v.reason
FROM student s
JOIN (
    -- 到課率
    SELECT student_id, check_time, '到課率' AS violation_type, count, class_hours, 
           CAST(NULL AS VARCHAR(50)) AS dorm_no, CAST(NULL AS VARCHAR(200)) AS reason
    FROM attendance_rate
    WHERE check_time BETWEEN :startTime AND :endTime
    
    UNION ALL
    
    -- 早鍛煉
    SELECT student_id, check_time, '早鍛煉', count, 
           CAST(NULL AS INT) AS class_hours, 
           CAST(NULL AS VARCHAR(50)) AS dorm_no, CAST(NULL AS VARCHAR(200)) AS reason
    FROM morning_exercise
    WHERE check_time BETWEEN :startTime AND :endTime
    
    UNION ALL
    
    -- 衛生
    SELECT student_id, check_time, '衛生', count, 
           CAST(NULL AS INT) AS class_hours, 
           dorm_no, reason
    FROM hygiene
    WHERE check_time BETWEEN :startTime AND :endTime
    
    UNION ALL
    
    -- 晚歸
    SELECT student_id, check_time, '晚歸', count, 
           CAST(NULL AS INT) AS class_hours, 
           dorm_no, CAST(NULL AS VARCHAR(200)) AS reason
    FROM late_return
    WHERE check_time BETWEEN :startTime AND :endTime
) v ON s.student_id = v.student_id
ORDER BY s.student_id, v.check_time;

關鍵技術點深度解析

1. 為什麼用 UNION ALL 而非 UNION？

• UNION：會去重（排序+比較），效能差，且業務上不需要去重
• UNION ALL：直接合併結果集，**不去重、不排序**，效能高數倍
• 違紀記錄業務上允許同一學生同一時間有多條不同類型記錄，**必須用 UNION ALL**

2. 字段類型對齊：CAST(NULL AS Type) 的必要性

UNION ALL 要求對應位置字段**數據類型完全一致**。各表字段定義不同（如 class_hours 可能是 INT，dorm_no 是 VARCHAR），必須顯式轉換 NULL 為目標類型：

-- 錯誤：類型不匹配
SELECT ..., NULL AS class_hours, NULL AS dorm_no FROM ...

-- 正確：顯式指定類型
SELECT ..., CAST(NULL AS INT) AS class_hours, CAST(NULL AS VARCHAR(50)) AS dorm_no FROM ...

3. 時間過濾下推：各子查詢獨立 WHERE

在每個子查詢內部加上 WHERE check_time BETWEEN ...，讓資料庫**在合併前就過濾掉不符合時間區間的數據**，大幅減少合併結果集大小，提升效能。

4. 增加 violation_type 識別來源

靜態字面值 '到課率'、'早鍛煉' 等作為識別列，方便應用層展示和後續統計分組。

性能優化進階方案

方案一：建立聯合視圖（推薦）

CREATE VIEW v_all_violations AS
SELECT student_id, check_time, '到課率' AS type, count, class_hours, NULL AS dorm_no, NULL AS reason FROM attendance_rate
UNION ALL
SELECT student_id, check_time, '早鍛煉', count, NULL, NULL, NULL FROM morning_exercise
UNION ALL
SELECT student_id, check_time, '衛生', count, NULL, dorm_no, reason FROM hygiene
UNION ALL
SELECT student_id, check_time, '晚歸', count, NULL, dorm_no, NULL FROM late_return;

-- 查詢簡化為
SELECT s.*, v.* FROM student s JOIN v_all_violations v ON s.student_id = v.student_id
WHERE v.check_time BETWEEN :start AND :end ORDER BY s.student_id, v.check_time;

方案二：分區表/分表（海量數據）

若單表超過千萬行，考慮按時間分區或分表，配合分區裁剪查詢。

方案三：彙總表/物化視圖（高頻查詢）

定時任務預聚合每日/每小時統計，查詢直接讀取彙總表。

索引建議

-- 每張表必須建立時間+學生ID複合索引
CREATE INDEX idx_attendance_time_student ON attendance_rate(check_time, student_id);
CREATE INDEX idx_exercise_time_student ON morning_exercise(check_time, student_id);
CREATE INDEX idx_hygiene_time_student ON hygiene(check_time, student_id);
CREATE INDEX idx_late_time_student ON late_return(check_time, student_id);

總結：UNION ALL + 字段類型顯式對齊 + 子查詢過濾下推 是多表同構數據高效合併查詢的標準模式。配合視圖封裝和索引優化，可支撐百萬級數據的毫秒級響應。