题解 | 统计加班员工占比

select
    staff_tb.department,
    concat(
        round(
            (count(t1.department) / count(staff_tb.department)) * 100,
            1
        ),
        '%'
    ) as ratio
from
    (
        select
            staff_tb.staff_id,
            staff_name,
            department,
            round(
                timestampdiff(minute, first_clockin, last_clockin) / 60,
                1
            ) as worktime
        from
            staff_tb
            join attendent_tb a on staff_tb.staff_id = a.staff_id
        where
            round(
                timestampdiff(minute, first_clockin, last_clockin) / 60,
                1
            ) > 9.5
    ) as t1
    right join staff_tb on t1.staff_id = staff_tb.staff_id
group by
    staff_tb.department
order by
    ratio desc