基于考勤日志创建工时视图：匹配IN/OUT记录及异常处理

问题分析 #

原代码用ROW_NUMBER()关联IN/OUT记录时，大概率用了INNER JOIN，导致只有成对的IN/OUT能被返回，单独的OUT（或IN）因为没有匹配项被过滤掉了。要解决这个问题，核心是用**全连接（FULL JOIN）**保留所有IN和OUT记录，同时给两类记录分别编号做关联。

解决方案SQL #

WITH in_records AS (
    SELECT
        Employee_id,
        Date,
        Time AS in_time,
        ROW_NUMBER() OVER (PARTITION BY Employee_id, Date ORDER BY Time) AS rn
    FROM logs
    WHERE Type = 'IN'
),
out_records AS (
    SELECT
        Employee_id,
        Date,
        Time AS out_time,
        ROW_NUMBER() OVER (PARTITION BY Employee_id, Date ORDER BY Time) AS rn
    FROM logs
    WHERE Type = 'OUT'
)
SELECT
    COALESCE(i.Employee_id, o.Employee_id) AS Employee_id,
    COALESCE(i.Date, o.Date) AS Date,
    i.in_time,
    o.out_time,
    CASE
        WHEN i.in_time IS NULL THEN '异常：无打卡入记录'
        WHEN o.out_time IS NULL THEN '异常：无打卡出记录'
        ELSE '正常'
    END AS DESCRIPTION
FROM in_records i
FULL JOIN out_records o
    ON i.Employee_id = o.Employee_id
    AND i.Date = o.Date
    AND i.rn = o.rn
ORDER BY Employee_id, Date, COALESCE(i.rn, o.rn);

代码说明 #

• 用两个CTE分别提取IN和OUT记录，各自按员工ID+日期分组，按时间排序生成行号rn，这样单日多次进出的记录会按顺序一一对应。
• 使用FULL JOIN关联两个CTE，确保单独的IN或OUT记录不会被过滤：
  • 当只有OUT记录时，i.in_time为NULL，DESCRIPTION会标记异常；
  • 当只有IN记录时，o.out_time为NULL，同样标记异常；
  • 成对的记录则显示正常。
• COALESCE()用来处理连接后可能出现的NULL值，确保员工ID、日期和行号能正常显示。

额外说明 #

如果存在单日IN记录数和OUT记录数不一致的情况（比如3次IN、2次OUT），多出来的IN会被标记为无打卡出，少的OUT会被标记为无打卡入，完全符合需求里的异常说明要求。

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