Azure SQL MI中如何查询违反连续两天工作时长超24小时规则的员工记录

嗨，我完全理解你在Azure SQL MI里遇到的这个查询难题了！咱们直接针对你的需求来设计解决方案。

首先，梳理下核心规则：我们要找连续两天都是工作状态（LaborType='work'），且这两天的工作时长总和超过24小时的员工记录，同时要把违规的两天日期合并展示，还要算出总工时。

解决方案思路 #

因为每个员工每天只有一条记录，我们可以用窗口函数LEAD()来关联每个员工当前日期的下一条记录（也就是第二天的记录），这样就能轻松对比连续两天的工作状态和时长了。具体步骤如下：

1. 用LEAD()按员工分组、日期排序，获取第二天的日期、工时和工作类型；
2. 筛选出当前天和第二天都是工作状态，且工时总和>24的记录；
3. 合并两天日期，计算总工时，整理成你需要的输出格式。

完整T-SQL代码 #

-- 先准备你的测试表（如果还没创建的话）
CREATE TABLE dbo.timesheet_mockup
(
    EmployeeID int,
    DateWorked date,
    LaborType varchar(7),
    Hours int
);

INSERT INTO dbo.timesheet_mockup
VALUES
    (1, '2024-01-01', 'work', 13),
    (1, '2024-01-02', 'work', 12),
    (1, '2024-01-03', 'no work', 24),
    (1, '2024-01-04', 'work', 8),
    (2, '2024-01-01', 'no work', 24),
    (2, '2024-01-02', 'work', 11),
    (2, '2024-01-03', 'work', 8),
    (2, '2024-01-04', 'work', 13);

-- 核心查询语句
WITH consecutive_work_days AS (
    SELECT
        EmployeeID,
        LaborType,
        DateWorked AS CurrentDate,
        Hours AS CurrentHours,
        -- 获取下一天的日期、工时、工作类型
        LEAD(DateWorked) OVER (PARTITION BY EmployeeID ORDER BY DateWorked) AS NextDate,
        LEAD(Hours) OVER (PARTITION BY EmployeeID ORDER BY DateWorked) AS NextHours,
        LEAD(LaborType) OVER (PARTITION BY EmployeeID ORDER BY DateWorked) AS NextLaborType
    FROM dbo.timesheet_mockup
)
SELECT
    EmployeeID,
    CONCAT(CurrentDate, ', ', NextDate) AS ViolationDates,
    CurrentHours + NextHours AS TotalHoursWorked
FROM consecutive_work_days
WHERE
    -- 确保两天都是工作状态
    LaborType = 'work'
    AND NextLaborType = 'work'
    -- 工时总和超过24小时
    AND (CurrentHours + NextHours) > 24;

结果说明 #

运行这段代码后，会得到你预期的输出：

这个查询只会返回每组违规的连续两天一次，不会重复统计；如果遇到连续三天都违规的情况（比如第一天+第二天超24，第二天+第三天也超24），会分别返回两组记录，完全符合业务规则。

备注：内容来源于stack exchange，提问作者skyline01