题解 | 统计加班员工占比

SELECT 
    department, CONCAT(ROUND(COUNT(IF(hours>9.5 , 1, null))/COUNT(*)*100,1),'%') as ratio
FROM
    (
        SELECT 
            st.staff_id as staff_id,
            st.department as department,
            (TIMESTAMPDIFF(MINUTE, at.first_clockin, at.last_clockin) / 60.0) AS hours
        FROM
            staff_tb as st
            LEFT JOIN attendent_tb as at ON st.staff_id = at.staff_id
    ) as re
GROUP BY department
ORDER BY ratio DESC