Excel VBA中员工连续B类记录时段的SQL查询求助

我有一张名为t的表格，字段及含义如下：

• fecha：日期
• employee：员工
• motive：事由
• descrip：描述

表格具体数据：

我需要得到以下查询结果，即每个员工连续出现motive='B'的时间段的起止日期：

该查询需在Excel VBA编辑器中运行，但我尝试的两条SQL语句无法得到目标输出：

select 
    employee, min(fecha), max(fecha)
from
    (select 
         t.*,
         lag(motive) over (partition by employee order by fecha) as prev_motive,
         lead(motive) over (partition by employee order by fecha) as next_motive,
         sum(case when motive = 'B' then 1 else 0 end) over (partition by employee order by fecha) as num_b
     from t) t
where 
    motive = 'B' 
    and (prev_motive <> 'B' or prev_motive is null) 
    and (next_motive <> 'B' or next_motive is null)
group by 
    employee, num_b;

select 
    employee, min(fecha), max(fecha)
from
    (select
         t.*,
         lag(motive) over (partition by employee order by fecha) as prev_motive,
         lead(motive) over (partition by employee order by fecha) as next_motive,
         sum(case when motive = 'B' then 1 else 0 end) over (partition by employee order by fecha) as num_b
     from t) t
where 
    motive = 'B' 
    and (prev_motive <> 'B') 
    and (next_motive <> 'B')
group by 
    employee, num_b;

解决方案 #

你的SQL问题在于筛选条件只保留了连续B段中同时满足前后行都不是B的记录，这会漏掉连续B段的中间行，导致分组聚合无法正确获取完整的起止日期。正确的做法是用岛屿和缺口算法，给每个连续的B组分配唯一分组ID，再按分组聚合日期。

以下是适配Excel VBA环境的SQL语句（主流Office版本支持窗口函数，旧版需用下方兼容方案）：

SELECT 
    employee,
    MIN(fecha) AS start_date,
    MAX(fecha) AS end_date
FROM (
    SELECT 
        t.*,
        -- 给连续的B组分配分组ID：当前行是B且上一行不是B时，累加计数
        SUM(CASE WHEN motive = 'B' AND (prev_motive <> 'B' OR prev_motive IS NULL) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY employee ORDER BY fecha) AS group_id
    FROM (
        SELECT 
            *,
            LAG(motive) OVER (PARTITION BY employee ORDER BY fecha) AS prev_motive
        FROM t
    ) t
    WHERE motive = 'B' -- 仅保留事由为B的行
) t
GROUP BY employee, group_id
ORDER BY employee, start_date;

逻辑说明： #

1. 内层子查询：用LAG获取每个员工上一行的事由，标记为prev_motive。
2. 中间子查询：按员工日期排序，当当前行是B且上一行不是B（或为第一行）时，累加生成group_id，连续的B行会被分到同一分组。
3. 外层查询：按员工和group_id分组，取每组最小、最大日期，即为该连续B段的起止日期。

如果你的Excel版本不支持窗口函数，可使用自关联兼容方案：

SELECT 
    t1.employee,
    MIN(t1.fecha) AS start_date,
    MAX(t1.fecha) AS end_date
FROM t t1
WHERE t1.motive = 'B'
AND NOT EXISTS (
    SELECT 1 
    FROM t t2
    WHERE t2.employee = t1.employee
    AND t2.fecha = DATEADD(day, -1, t1.fecha)
    AND t2.motive = 'B'
)
GROUP BY t1.employee, (
    SELECT COUNT(*) 
    FROM t t3
    WHERE t3.employee = t1.employee
    AND t3.fecha <= t1.fecha
    AND t3.motive <> 'B'
)
ORDER BY t1.employee, start_date;

该方案通过统计当前日期之前非B行的数量生成分组ID，同样可实现连续B段的分组聚合。

内容的提问来源于stack exchange，提问作者Patricio Hernandez Ballester