题解 | 统计加班员工占比

select
    department,
    concat(
        round(
            count(distinct IF(work_time > 9.5, staff_id, NULL)) / count(distinct staff_id) * 100,
            1
        ),
        "%"
    ) as ratio
from
    (
        select
            staff_id,
            department
        from
            staff_tb
    ) t1
    left join (
        select
            staff_id,
            round(
                timestampdiff(minute, first_clockin, last_clockin) / 60,
                1
            ) as work_time
        from
            attendent_tb
    ) t2 using (staff_id)
group by
    department
order by ratio desc