题解 | 统计加班员工占比

select *
from(select t1.department ,
case  when overtime/totalnumber is null then concat(round(0*100.0,1),'%')
else  concat(round(overtime/totalnumber*100,1),'%')
end  ratio
from
(select department ,count(department) totalnumber
from  staff_tb
group by department
)t1  left join 
(select department,count(workhour) overtime
from  staff_tb  join
(
select staff_id, round(timestampdiff(second,first_clockin,last_clockin)/3600,1) workhour
from attendent_tb
)t
on  staff_tb.staff_id= t.staff_id
where workhour>9.5 
group by department )t2
on t1.department=t2.department
)t3
order by ratio desc