SQL Server按小时统计在岗员工数技术实现问询

我来帮你搞定这个统计需求！核心思路是先生成我们需要统计的所有小时区间，再逐个检查每个员工的在岗时间段是否和这些小时重叠，最后统计每个小时的在岗人数。

步骤1：生成目标时间段的所有小时序列 #

首先我们需要生成从@ontem到@hoje之间的每小时起始时间，这可以通过CTE（公共表表达式）来生成连续的时间点，确保不会漏掉任何一个需要统计的小时。

步骤2：关联在岗记录并统计人数 #

把你已有的打卡进出记录作为子查询，和生成的小时序列做关联，判断员工的在岗时间段是否覆盖了当前小时，最后按小时分组计数即可。

完整SQL代码 #

declare @ontem datetime
declare @hoje datetime
set @ontem = CAST(CONVERT(VARCHAR(10), GETDATE() - 1, 101) + ' 06:00:00' AS DATETIME)
set @hoje = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) + ' 06:00:00' AS DATETIME)

-- 生成连续的小时序列，覆盖目标统计区间
WITH HourlyIntervals AS (
    SELECT @ontem AS HourStart
    UNION ALL
    SELECT DATEADD(HOUR, 1, HourStart)
    FROM HourlyIntervals
    WHERE DATEADD(HOUR, 1, HourStart) <= @hoje
),
-- 获取员工的打卡进出记录（复用你原有的查询逻辑）
EmployeeShifts AS (
    SELECT a1.Operator, a1.Time as clockIN,
    (SELECT top 1 a2.Time FROM dg01.dbo.wgclogfilepos a2 where a1.Operator = a2.Operator and a2.Event = 13 and a1.Time >= @ontem and a1.Time <= @hoje and a2.Time > a1.Time order by a2.Time) AS clockout
    FROM dg01.dbo.wgclogfilepos a1
    WHERE Event = 12 and a1.Time >= @ontem and a1.Time <= @hoje
)
-- 统计每个小时的在岗人数
SELECT 
    FORMAT(hi.HourStart, 'HH:mm') AS [Time],
    COUNT(DISTINCT es.Operator) AS [Count]
FROM HourlyIntervals hi
LEFT JOIN EmployeeShifts es 
    ON es.clockIN < DATEADD(HOUR, 1, hi.HourStart) 
    AND es.clockout > hi.HourStart
GROUP BY hi.HourStart
ORDER BY hi.HourStart
OPTION (MAXRECURSION 0); -- 解除递归次数限制，支持跨多天的统计

代码细节解释 #

• HourlyIntervals：用递归CTE生成从@ontem（前一天6点）到@hoje（当天6点）的每小时起始时间，比如2018-01-27 06:00:00、2018-01-27 07:00:00直到2018-01-28 06:00:00。
• EmployeeShifts：直接复用你原有的查询逻辑，获取每个员工的打卡进入和离开时间。
• 关联判断逻辑：只要员工的进入时间小于当前小时的结束时间（HourStart + 1小时），且离开时间大于当前小时的起始时间，就说明这个小时内该员工处于在岗状态。
• 格式化输出：用FORMAT函数把小时起始时间转成HH:mm的格式，完全匹配你需要的输出样式。
• OPTION (MAXRECURSION 0)：默认递归CTE的递归次数上限是100，加上这个选项可以支持跨多天的统计场景，避免报错。

运行这段代码后，就能得到每个小时的在岗员工数量，包括那些在岗人数为0的小时哦！

内容的提问来源于stack exchange，提问作者Marcelo Marinho de Araujo