题解 | 统计加班员工占比

SELECT t2.department,CONCAT(ROUND((COUNT(t1.staff_id)/COUNT(t2.staff_id))*100,1),'%') ratio
FROM(
    SELECT staff_id,timestampdiff(second,first_clockin,last_clockin)/3600 D_diff
    FROM attendent_tb
    WHERE timestampdiff(second,first_clockin,last_clockin)/3600 > 9.5
) t1
RIGHT JOIN staff_tb t2
ON t1.staff_id = t2.staff_id
GROUP BY t2.department
ORDER BY ratio DESC