SQL 查詢缺卡記錄：利用交叉連接生成完整日期表再差集

表 A 存打卡記錄，表 B 存工作日日期。需找出「本應打卡卻無記錄」的員工日期組合。核心思路：員工 × 工作日 = 理論全集，再 EXCEPT 實際打卡集。

標準 SQL（通用語法）

WITH EmpDays AS (
    SELECT e.EmpId, e.Name, w.WorkDate
    FROM (SELECT DISTINCT EmpId, Name FROM A) e
    CROSS JOIN (SELECT DISTINCT CAST(WorkTime AS DATE) AS WorkDate FROM B) w
),
ActualDays AS (
    SELECT EmpId, CAST(StartTime AS DATE) AS WorkDate FROM A
)
SELECT ed.EmpId, ed.Name, ed.WorkDate AS 缺卡日期
FROM EmpDays ed
LEFT JOIN ActualDays ad 
    ON ed.EmpId = ad.EmpId AND ed.WorkDate = ad.WorkDate
WHERE ad.EmpId IS NULL
ORDER BY ed.EmpId, ed.WorkDate;

關鍵點

• CROSS JOIN 生成笛卡爾積，得到每位員工每個工作日的理論記錄。
• 日期類型統一用 DATE，避免時間部分干擾匹配。
• 若數據量大（百萬級），確保 A(EmpId, StartTime)、B(WorkTime) 有索引，或將 EmpDays 物化為臨時表。

MySQL 8.0+ 簡寫

SELECT e.EmpId, e.Name, w.WorkDate
FROM (SELECT DISTINCT EmpId, Name FROM A) e
JOIN (SELECT DISTINCT DATE(WorkTime) WorkDate FROM B) w
LEFT JOIN A a ON a.EmpId=e.EmpId AND DATE(a.StartTime)=w.WorkDate
WHERE a.EmpId IS NULL;