题解 | 统计加班员工占比

SELECT department, CONCAT(ROUND(SUM(IF(TIMESTAMPDIFF(MINUTE,first_clockin, last_clockin) > 9.5*60, 1, 0))/COUNT(a.staff_id)*100,1),'%') AS ratio
FROM attendent_tb a
LEFT JOIN staff_tb ON a.staff_id = staff_tb.staff_id
GROUP BY department
ORDER BY ratio DESC;

注：

1. TIMESTAMPDIFF(HOUR, first_clockin, last_clockin)会向下取整到最接近的整数，无法准确判断时间差是否超过9.5小时。如果时间差是9.6小时，该函数会返回9，时间差是9.3时，也会返回9。
2. 要准确判断两个时间点之间的时间差是否超过9.5小时，使用TIMESTAMPDIFF函数计算分钟数差是更合适的选择，TIMESTAMPDIFF(MINUTE,first_clockin, last_clockin) > 9.5*60