题解 | 统计加班员工占比
WITH work AS( SELECT * FROM attendent_tb WHERE TIMESTAMPDIFF(minute, first_clockin, last_clockin)/60 > 9.5 ) SELECT f.department, CONCAT(ROUND((COUNT(work.staff_id)/COUNT(a.staff_id))*100,1), '%') AS ratio FROM staff_tb AS f LEFT JOIN attendent_tb AS a ON f.staff_id = a.staff_id LEFT JOIN work ON f.staff_id = work.staff_id GROUP BY f.department ORDER BY ratio DESC;
