题解 | 统计加班员工占比

SELECT
    t.department,
    CONCAT(ROUND(t.IS_JIABAN/staff_num*100, 1), "%") AS ratio
FROM(
    SELECT
        department,
        COUNT(s.staff_id) AS staff_num,
        SUM(CASE WHEN TIMESTAMPDIFF(minute, first_clockin, last_clockin)/60 > 9.5 THEN 1
        ELSE 0
        END) AS IS_JIABAN
    FROM staff_tb s  JOIN attendent_tb a
    ON s.staff_id = a.staff_id
    GROUP BY department ) t
ORDER BY ratio DESC