SQL查询：考勤表存在NULL时显示NULL，否则取MAX(End)值

需求说明 #

我有一张名为time_captured的考勤数据表，需要生成按用户去重的结果表，满足以下规则：

• 每个用户对应最小的Start值
• 若该用户存在End字段为NULL的记录，则End列显示NULL；否则取最大的End值

原表数据 #

time_captured表的原始数据：

期望结果 #

期望生成的聚合结果表：

问题SQL及原因 #

我尝试了以下SQL代码，但返回的是原表数据，未达到预期效果：

SELECT
MIN(time_captured.start_time),
CASE 
WHEN
time_captured.end_time IS NULL THEN NULL
ELSE
MAX(time_captured.end_time)
END,
time_captured.user_sign
FROM
time_captured
GROUP BY
time_captured.user_sign,
time_captured.end_time

错误原因 #

1. GROUP BY子句中额外包含了end_time，这会让每个不同的end_time值都生成独立分组，无法实现按用户单独聚合的目标。
2. CASE语句中直接引用time_captured.end_time不符合聚合查询规则——聚合查询中只能引用分组字段或经过聚合函数处理的字段。

正确SQL实现 #

方法一：使用EXISTS子查询 #

SELECT
    user_sign AS User,
    MIN(start_time) AS Start,
    CASE
        WHEN EXISTS (SELECT 1 FROM time_captured t2 WHERE t2.user_sign = t1.user_sign AND t2.end_time IS NULL)
        THEN NULL
        ELSE MAX(end_time)
    END AS End
FROM time_captured t1
GROUP BY user_sign;

方法二：使用聚合函数判断NULL存在 #

SELECT
    user_sign AS User,
    MIN(start_time) AS Start,
    CASE
        WHEN MAX(CASE WHEN end_time IS NULL THEN 1 ELSE 0 END) = 1
        THEN NULL
        ELSE MAX(end_time)
    END AS End
FROM time_captured
GROUP BY user_sign;

代码说明 #

• 两种方法都仅按user_sign分组，确保每个用户只返回一条记录。
• 方法一通过EXISTS子查询直接检查当前用户是否存在end_time为NULL的记录，逻辑直观。
• 方法二通过嵌套CASE生成0/1标记，用MAX判断是否存在NULL记录，无需额外子查询，性能更优。

内容的提问来源于stack exchange，提问作者William Piah