题解 | 统计加班员工占比

SELECT
    st.department,
    CONCAT (
        ROUND(
            SUM(
                CASE
                    WHEN TIMESTAMPDIFF (MINUTE, at.first_clockin, at.last_clockin)/60 > 9.5 THEN 1
                    ELSE 0
                END
            ) / COUNT(st.staff_id) * 100,
            1
        ),
        '%'
    ) AS ratio
FROM
    staff_tb st
    JOIN attendent_tb at ON st.staff_id = at.staff_id
GROUP BY
    st.department
ORDER BY
    ratio DESC

TIMESTAMPDIFF(HOUR, ...)，小时为整数; TIMESTAMPDIFF(MINUTE, ...) / 60.0，小时为浮点数, 更精确