题解 | 统计加班员工占比

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