题解 | 统计加班员工占比

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