如何用SQL筛选连续病假满30天的员工？含多病假单衔接场景

嘿，这个问题是SQL里处理连续时间段的经典场景，我来帮你搞定它～首先得确认下：你的病假表应该还有一个employee_id字段吧？毕竟要区分不同员工的病假记录，我下面的方案就默认包含这个字段啦。

核心思路 #

我们需要把同一员工的衔接或重叠的病假单合并成连续的时间段，然后计算每个连续时间段的总时长，最后筛选出总时长≥30天的员工。

分步实现SQL代码 #

下面是通用的SQL方案，适配大多数支持窗口函数的数据库（比如PostgreSQL、MySQL 8.0+、SQL Server等）：

-- 第一步：给每个连续的病假段打分组标签
WITH grouped_sick_leaves AS (
    SELECT
        employee_id,
        date_from,
        date_to,
        duration,
        -- 判断当前病假是否和上一条连续：如果当前开始日期 ≤ 上一条结束日期+1天，属于同一组
        SUM(CASE 
            WHEN date_from <= LAG(date_to) OVER (PARTITION BY employee_id ORDER BY date_from) + INTERVAL '1 day' 
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY employee_id ORDER BY date_from) AS group_id
    FROM sick_leaves
),
-- 第二步：合并同一组的病假，计算总时长
merged_sick_periods AS (
    SELECT
        employee_id,
        MIN(date_from) AS continuous_start,
        MAX(date_to) AS continuous_end,
        SUM(duration) AS total_continuous_days
    FROM grouped_sick_leaves
    GROUP BY employee_id, group_id
)
-- 第三步：筛选出连续病假≥30天的员工
SELECT DISTINCT employee_id
FROM merged_sick_periods
WHERE total_continuous_days >= 30;

关键细节解释 #

1. 分组标签生成：

   • 用LAG()窗口函数获取当前员工的上一条病假结束日期，判断当前病假的date_from是否和上一条衔接（或重叠）。如果是，就和上一条归为同一组；否则开启新组。
   • SUM(...) OVER (...)是累计求和，用来生成唯一的group_id，同一连续段的所有记录会共享这个ID。

2. 合并时间段与计算时长：

   • 按employee_id和group_id分组后，取组内最早的date_from和最晚的date_to作为连续时间段的起止，再把组内的duration求和得到总时长。

3. 数据库适配调整：

   • 如果你的数据库不支持INTERVAL '1 day'语法（比如MySQL），可以改成DATE_ADD(LAG(date_to) OVER (...), INTERVAL 1 DAY)。
   • 如果duration字段不是预先计算好的，而是需要从date_from和date_to计算，那把SUM(duration)替换成对应的日期差函数：
     • PostgreSQL：SUM(DATE_PART('day', date_to - date_from) + 1)
     • MySQL：SUM(DATEDIFF(date_to, date_from) + 1)
     • SQL Server：SUM(DATEDIFF(day, date_from, date_to) + 1)

这样就能准确筛选出那些连续病假（包括衔接的多份病假单）总时长至少30天的员工啦～

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