SQL/PLSQL查询轮班员工各班次最小打卡时间问题

需求为查询轮班员工各班次的最小时间，部分员工单日存在2个班次。表t的结构及数据如下：

|     ID   |   date     |  time    |  status  |
| -------- | ---------- | -------- | -------- |           
|     1    | 2022-01-01 | 08:00:00 | Shift In | 
|     1    | 2022-01-01 | 08:15:00 | Shift In |
|     1    | 2022-01-01 | 10:30:00 | Shift Out|
|     1    | 2022-01-01 | 12:15:00 | Shift In |
|     1    | 2022-01-01 | 12:18:00 | Shift In |
|     1    | 2022-01-01 | 14:52:00 | Shift Out|
|     1    | 2022-01-01 | 15:00:00 | Shift Out|
|     2    | 2022-01-01 | 17:15:00 | Shift In |
|     2    | 2022-01-01 | 18:15:00 | Shift Out|
|     2    | 2022-01-01 | 18:18:00 | Shift Out|

期望输出每个班次对应的最早Shift In和Shift Out时间：

|     ID   |   date     |  time    |  status  |
| -------- | ---------- | -------- | -------- |           
|     1    | 2022-01-01 | 08:00:00 | Shift In | 
|     1    | 2022-01-01 | 10:30:00 | Shift Out|
|     1    | 2022-01-01 | 12:15:00 | Shift In |
|     1    | 2022-01-01 | 14:52:00 | Shift Out|
|     2    | 2022-01-01 | 17:15:00 | Shift In |
|     2    | 2022-01-01 | 18:15:00 | Shift Out|

SQL解决方案 #

使用窗口函数为每个班次生成唯一分组ID，再分组取最小时间：

WITH shift_groups AS (
    SELECT 
        ID,
        date,
        time,
        status,
        -- 为每个Shift In递增生成班次ID，Shift Out继承最近的班次ID
        SUM(CASE WHEN status = 'Shift In' THEN 1 ELSE 0 END) OVER (
            PARTITION BY ID, date 
            ORDER BY time 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS shift_id
    FROM t
)
SELECT 
    ID,
    date,
    MIN(time) AS time,
    status
FROM shift_groups
GROUP BY ID, date, status, shift_id
ORDER BY ID, date, time;

逻辑说明 #

1. 通过窗口函数SUM(...) OVER (...)，按员工ID和日期分区、时间排序，每遇到一次Shift In就将shift_id加1，后续的Shift Out自动归属于当前班次ID，实现班次分组。
2. 按员工ID、日期、状态和班次ID分组，取每组内的最小时间，即为该班次对应的最早签到/签退时间。
3. 最后按ID、日期、时间排序，得到符合要求的输出。

PL/SQL解决方案（可选） #

如果需要用PL/SQL实现，可通过游标遍历数据，跟踪当前班次的状态和最早时间：

DECLARE
    CURSOR c_shift IS
        SELECT ID, date, time, status
        FROM t
        ORDER BY ID, date, time;
    v_prev_id t.ID%TYPE;
    v_prev_date t.date%TYPE;
    v_current_shift_in_time t.time%TYPE;
    v_current_shift_out_time t.time%TYPE;
BEGIN
    FOR rec IN c_shift LOOP
        -- 切换员工或日期时，输出上一个班次的记录（如果存在）
        IF (v_prev_id IS NOT NULL AND (v_prev_id != rec.ID OR v_prev_date != rec.date)) THEN
            IF v_current_shift_in_time IS NOT NULL THEN
                DBMS_OUTPUT.PUT_LINE(v_prev_id || ' | ' || v_prev_date || ' | ' || v_current_shift_in_time || ' | Shift In');
            END IF;
            IF v_current_shift_out_time IS NOT NULL THEN
                DBMS_OUTPUT.PUT_LINE(v_prev_id || ' | ' || v_prev_date || ' | ' || v_current_shift_out_time || ' | Shift Out');
            END IF;
            v_current_shift_in_time := NULL;
            v_current_shift_out_time := NULL;
        END IF;

        -- 更新当前班次的最早时间
        IF rec.status = 'Shift In' THEN
            IF v_current_shift_in_time IS NULL OR rec.time < v_current_shift_in_time THEN
                v_current_shift_in_time := rec.time;
            END IF;
        ELSIF rec.status = 'Shift Out' THEN
            IF v_current_shift_out_time IS NULL OR rec.time < v_current_shift_out_time THEN
                v_current_shift_out_time := rec.time;
            END IF;
        END IF;

        v_prev_id := rec.ID;
        v_prev_date := rec.date;
    END LOOP;

    -- 输出最后一个员工的班次记录
    IF v_prev_id IS NOT NULL THEN
        IF v_current_shift_in_time IS NOT NULL THEN
            DBMS_OUTPUT.PUT_LINE(v_prev_id || ' | ' || v_prev_date || ' | ' || v_current_shift_in_time || ' | Shift In');
        END IF;
        IF v_current_shift_out_time IS NOT NULL THEN
            DBMS_OUTPUT.PUT_LINE(v_prev_id || ' | ' || v_prev_date || ' | ' || v_current_shift_out_time || ' | Shift Out');
        END IF;
    END IF;
END;
/

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