如何查询包含指定记录的连续员工工作表（时间间隔≤5分钟）

数据库表结构及数据 #

表名：employee_worksheet

需求说明 #

给定某条特定记录（例如ID为#3的记录），编写SQL查询获取包含该记录的所有连续班次。连续判定规则：后一班次的time_start与前一班次的time_end时间间隔不超过5分钟。期望输出如下：

解决方案 #

常规Gaps and Islands解法是全局划分岛屿，这里需要定位目标记录所在的岛屿，以下两种方法均可实现：

方法一：递归CTE（支持递归的数据库：PostgreSQL、MySQL 8+、SQL Server等） #

从目标记录出发，向上递归匹配所有符合连续规则的前置班次，向下递归匹配所有符合规则的后置班次，最后合并去重。

WITH RECURSIVE cte_up AS (
    -- 起始点：目标记录
    SELECT ID, employee_id, time_start, time_end
    FROM employee_worksheet
    WHERE ID = '#3' -- 替换为目标记录ID
    
    UNION ALL
    
    -- 向上查找连续前置班次：当前班次的time_start与前置班次time_end间隔≤5分钟
    SELECT ew.ID, ew.employee_id, ew.time_start, ew.time_end
    FROM employee_worksheet ew
    JOIN cte_up cu ON ew.employee_id = cu.employee_id
        AND ew.time_end >= DATE_SUB(cu.time_start, INTERVAL 5 MINUTE)
        AND ew.time_end < cu.time_start
),
cte_down AS (
    -- 起始点：目标记录
    SELECT ID, employee_id, time_start, time_end
    FROM employee_worksheet
    WHERE ID = '#3' -- 替换为目标记录ID
    
    UNION ALL
    
    -- 向下查找连续后置班次：后置班次time_start与当前班次time_end间隔≤5分钟
    SELECT ew.ID, ew.employee_id, ew.time_start, ew.time_end
    FROM employee_worksheet ew
    JOIN cte_down cd ON ew.employee_id = cd.employee_id
        AND ew.time_start <= DATE_ADD(cd.time_end, INTERVAL 5 MINUTE)
        AND ew.time_start > cd.time_end
)
-- 合并结果、去重并按时间排序
SELECT DISTINCT ID, employee_id, time_start, time_end
FROM (SELECT * FROM cte_up UNION ALL SELECT * FROM cte_down) AS combined
ORDER BY time_start;

方法二：定位目标岛屿边界（适配大多数数据库） #

1. 给同员工的班次按时间排序，标记每个班次是否为岛屿起点（与上一班次间隔>5分钟）；
2. 累计起点标记生成岛屿ID；
3. 找到目标记录所属的岛屿ID，筛选该岛屿的所有班次。

WITH ranked AS (
    SELECT 
        ID, 
        employee_id, 
        time_start, 
        time_end,
        -- 标记是否为岛屿起点
        CASE 
            WHEN TIMESTAMPDIFF(MINUTE, LAG(time_end) OVER (PARTITION BY employee_id ORDER BY time_start), time_start) > 5
            THEN 1 
            ELSE 0 
        END AS is_island_start
    FROM employee_worksheet
),
islands AS (
    SELECT 
        *,
        -- 累计起点标记生成岛屿ID
        SUM(is_island_start) OVER (PARTITION BY employee_id ORDER BY time_start) AS island_id
    FROM ranked
),
target_island AS (
    -- 获取目标记录的岛屿ID和员工ID
    SELECT island_id, employee_id
    FROM islands
    WHERE ID = '#3' -- 替换为目标记录ID
)
-- 筛选目标岛屿的所有班次并排序
SELECT i.ID, i.employee_id, i.time_start, i.time_end
FROM islands i
JOIN target_island ti ON i.employee_id = ti.employee_id AND i.island_id = ti.island_id
ORDER BY i.time_start;

注意事项 #

• 需将两处WHERE ID = '#3'中的#3替换为实际目标记录的ID；
• 时间函数因数据库差异需调整：
  • PostgreSQL：用EXTRACT(MINUTE FROM time_start - LAG(time_end)...)和time_start - INTERVAL '5 minutes'
  • SQL Server：用DATEDIFF(MINUTE, ...)和DATEADD(MINUTE, 5, ...)

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