SQL查询如何基于字段最值为结果集新增状态列

需求背景 #

现有考勤打卡表查询逻辑，需要新增打卡状态列，赋值规则如下：

• 按「员工+打卡日期」维度分组
• 组内最早打卡（punch_time为最小值）的记录标记为上班
• 组内最晚打卡（punch_time为最大值）的记录标记为下班
• 其余中间时段打卡记录可按需自定义赋值

原基础查询存在两处冗余：重复查询了两次punch_time字段，且因主键id唯一，原有GROUP BY逻辑无实际作用，可直接删除。

实现代码（适配SQL Server语法，和现有语句完全兼容） #

SELECT 
    a.id,
    a.terminal_id,
    a.emp_code,
    DATEADD(dd, 0, DATEDIFF(dd, 0, a.punch_time)) AS TranDate,
    a.punch_time,
    a.punch_state,
    a.area_alias,
    -- 新增状态列赋值逻辑
    CASE
        WHEN a.punch_time = MIN(a.punch_time) OVER (PARTITION BY a.emp_code, DATEADD(dd, 0, DATEDIFF(dd, 0, a.punch_time))) THEN '上班'
        WHEN a.punch_time = MAX(a.punch_time) OVER (PARTITION BY a.emp_code, DATEADD(dd, 0, DATEDIFF(dd, 0, a.punch_time))) THEN '下班'
        ELSE '' -- 非上下班的中间打卡记录可按需赋值，比如'外出'、'返回'
    END AS punch_status
FROM iclock_transaction a 
WHERE a.emp_code = '8734'  
  AND DATEDIFF(day, a.punch_time, '2022-06-07') = 0

逻辑说明 #

• 采用窗口函数MIN() OVER()、MAX() OVER()实现分组最值计算，按「员工编号+打卡日期」做分区，不需要额外关联子查询，执行效率更高。
• 分区逻辑自带员工维度隔离，后续如果需要查询全员工的打卡状态，只要去掉WHERE条件里的员工编号筛选即可，不会出现跨员工取最值的问题。
• 若单日员工只有1条打卡记录，会优先匹配第一个判断条件标记为「上班」，如果需要调整这类异常场景的标记规则，可自行修改CASE判断顺序或增加条件分支。

内容的提问来源于stack exchange，提问作者Lyrad Em Em Onidrareg