题解 | 统计加班员工占比

# 考察左连接的使用
select department,concat(round(count(a.staff_id)/count(s.staff_id)*100,1),'%') ratio
from staff_tb s left join attendent_tb a 
on a.staff_id = s.staff_id
and timestampdiff(minute,first_clockin,last_clockin) > 570
group by department
order by ratio desc
# 以部门员工信息表为主表
# 左连接出勤表,链接条件是出勤时长超过9.5小时,那么未加班的员工id就变成了null
# 右表加班的员工数/员工全表的id数为加班比率
# 员工信息为全表则即使没有加班的也会被显示出来