题解 | #牛客直播各科目出勤率#

select t6.course_id,t5.course_name
,round(t6.chuqin*100/t6.baoming,2) from (
select t4.course_id
,count(distinct t4.chuqin_ren) as chuqin
,count(distinct t4.baoming_ren) as baoming
from (
select  t3.course_id 
,(case when (t3.diff >=10 and t3.if_sign = 1 ) then t3.user_id else null end ) as chuqin_ren
,(case when t3.if_sign = 1 then t3.user_id else null end ) as baoming_ren
from (
select t2.user_id,t2.course_id
,timestampdiff(minute,t1.in_datetime,t1.out_datetime) as diff 
,t2.if_sign 
from attend_tb as t1 right join behavior_tb as t2 
on t1.course_id = t2.course_id and t1.user_id=t2.user_id) t3 
) t4 group by t4.course_id
) t6 
left join course_tb as t5 on t6.course_id = t5.course_id 
order by t6.course_id