MySQL中CASE条件下DATE与DATETIME字段对比存在异常判定问题

核心问题1：GROUP BY后的字段引用逻辑错误 #

你的SQL存在致命逻辑漏洞：CASE表达式中使用的start_event是原表的未聚合字段，而非SELECT中指定的MIN(start_event)。

在MySQL未开启ONLY_FULL_GROUP_BY模式时，GROUP BY后直接引用未做聚合的字段（如e.start_event），MySQL会随机返回分组内某一条记录的对应值，而非你期望的聚合后最小值。

以你的异常案例为例：

• 当天该员工可能存在多条原title='CHECKED'的打卡记录，比如一条是2022-11-17 07:14:00，另一条是2022-11-17 08:05:00（原title错误标记为CHECKED）。
• GROUP BY后，SELECT显示的start_event是聚合后的最小值07:14:00，但CASE表达式计算时却随机取了08:05:00这条记录的start_event。
• 此时计算时差为08:05-07:00=65分钟，符合MAJOR DELAY的判定条件，最终导致显示的打卡时间和标记结果完全不匹配。

核心问题2：WHERE子句过滤逻辑不符合需求 #

你在WHERE中添加了title = 'CHECKED'，这会只筛选events表中原已标记为CHECKED的记录，但你的需求是重新计算所有打卡记录的延迟标记，而非仅处理原标记正确的记录。这会导致你无法修正原表中标记错误的记录，同时限制了分组的数据源范围。

核心问题3：时间计算存在隐式转换风险 #

DATE_FORMAT(start_event, '%Y-%m-%d') + INTERVAL TIME_TO_SEC(entry) SECOND依赖MySQL的隐式类型转换，将字符串格式的日期转为可计算的时间类型，在某些场景下可能出现计算偏差。更可靠的写法是使用TIMESTAMP(DATE(start_event), entry)直接组合日期和时间，得到当天的入职时间点。

修正后的SQL #

SELECT 
    e.num_employee AS num_employee,
    MIN(start_event) AS start_event,
    -- 若需要end_event，建议使用聚合函数（如MAX(end_event)），根据业务需求选择
    MAX(end_event) AS end_event,
    CASE 
        WHEN TIMESTAMPDIFF(MINUTE, TIMESTAMP(DATE(MIN(start_event)), em.entry), MIN(start_event)) > 15
             AND TIMESTAMPDIFF(MINUTE, TIMESTAMP(DATE(MIN(start_event)), em.entry), MIN(start_event)) <= 40
        THEN 'MINOR DELAY' 
        WHEN TIMESTAMPDIFF(MINUTE, TIMESTAMP(DATE(MIN(start_event)), em.entry), MIN(start_event)) > 40
             AND TIMESTAMPDIFF(MINUTE, TIMESTAMP(DATE(MIN(start_event)), em.entry), MIN(start_event)) <= 90
        THEN 'MAJOR DELAY'
        ELSE 'CHECKED' 
    END AS title
FROM events AS e 
JOIN employees AS em ON e.num_employee = em.num_employee
WHERE e.num_employee = $num_employee
-- 移除原title的过滤，确保所有打卡记录都参与计算
GROUP BY num_employee, DATE(start_event)

额外优化建议 #

1. 开启MySQL的ONLY_FULL_GROUP_BY模式，强制GROUP BY的字段和SELECT中非聚合字段一致，从根源避免此类隐性逻辑错误。
2. 确保聚合逻辑和计算逻辑使用同一个时间值（即MIN(start_event)），保证数据源的一致性。

内容的提问来源于stack exchange，提问作者Andrea Duran