SQL如何按公司、日期分组统计总工时及分配/未分配工时
解决SQL语句(适配MySQL环境)
SELECT company_id, -- 计算总工时,如需展示分钟可修改TIME_FORMAT参数为'%H hours %i minutes' CONCAT( TIME_FORMAT(SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, start_date, end_date))), '%H hours'), ' total' ) AS total_hours, -- 计算已分配工时&员工数 CONCAT( TIME_FORMAT(SEC_TO_TIME(SUM(CASE WHEN employee_id IS NOT NULL THEN TIMESTAMPDIFF(SECOND, start_date, end_date) ELSE 0 END)), '%H hours'), ' assigned', IF(COUNT(employee_id) > 0, CONCAT(' to ', COUNT(employee_id), ' employees'), '') ) AS total_assigned, -- 计算未分配工时 CONCAT( TIME_FORMAT(SEC_TO_TIME(SUM(CASE WHEN employee_id IS NULL THEN TIMESTAMPDIFF(SECOND, start_date, end_date) ELSE 0 END)), '%H hours'), ' unassigned' ) AS total_unassigned, DATE(start_date) AS start_date, DATE(MAX(end_date)) AS end_date FROM 替换为实际表名 GROUP BY company_id, DATE(start_date);
核心逻辑说明
- 总工时直接聚合所有行的起止时间差,无需过滤
- 已分配工时通过
CASE WHEN条件筛选employee_id不为空的行统计时长,COUNT(employee_id)会自动忽略空值,统计有效分配的员工数量 - 未分配工时可直接统计
employee_id为空的行时长,也可以用总工时减去已分配工时得到,两种方式结果一致 - 非MySQL环境只需替换对应时间差计算、秒转时间格式的函数即可,条件聚合的核心逻辑通用
内容的提问来源于stack exchange,提问作者Daniel Barnes
相关产品推荐
相关产品推荐

