SQL统计员工周工时sum仅返回单班次总和而非整周总和问题

现有SQL的错误点 #

• INNER JOIN没有写关联条件，会生成两个表的笛卡尔积，产出大量无效的关联数据，直接导致统计结果失真。
• 分组字段包含了单班次的打卡时间s.InTime，相当于每个班次单独分为一组，自然sum(Hours)返回的就是单个班次的时长，无法实现按周聚合的效果。
• SELECT中加的DISTINCT完全冗余，已经通过GROUP BY分组的查询结果本身就不会出现重复行。
• 周计算逻辑依赖数据库默认的DATEFIRST配置，如果环境配置的周起始日不是周日，会导致周区间计算错误，建议显式指定避免环境差异。

修正后的SQL（兼容SQL Server语法） #

-- 显式指定一周的第一天为周日，避免不同环境配置导致的计算偏差
SET DATEFIRST 7;

SELECT 
    s.EmployeeID,
    CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,
    DATEADD(WEEK, DATEDIFF(WEEK, 0, s.InTime), -1) AS WeekStarting,
    DATEADD(DAY, 6, DATEADD(WEEK, DATEDIFF(WEEK, 0, s.InTime), -1)) AS WeekEnding,
    SUM(s.Hours) AS HoursWorkedInWeek
FROM Shifts s
-- 补充员工ID作为关联条件
INNER JOIN EmployeePayroll e ON s.EmployeeID = e.EmployeeID
-- 分组仅保留员工维度+周维度，不需要加入单班次的打卡时间
GROUP BY 
    s.EmployeeID,
    e.FirstName,
    e.LastName,
    DATEDIFF(WEEK, 0, s.InTime)

-- 如果需要查询指定周，直接添加WHERE条件即可，示例如下：
-- WHERE DATEDIFF(WEEK, 0, s.InTime) = DATEDIFF(WEEK, 0, '2024-05-19')

修正后相同员工同一周的所有班次都会被归入同一组做求和计算，即可得到正确的整周总工时。

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