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

select a.course_id,a.course_name,round(sum(attend)/sign_num*100,2)
from course_tb a
left join
(
select b.user_id,b.course_id,count(distinct(b.user_id)) as attend
from attend_tb b
left join behavior_tb c
on b.user_id=c.user_id and b.course_id=c.course_id
where if_sign=1
group by b.course_id,b.user_id
having sum(timestampdiff(minute,in_datetime,out_datetime))>=10
) t1
on a.course_id=t1.course_id
left join
(select course_id,count(*) as sign_num
from behavior_tb
where if_sign=1
group by course_id
)t2
on a.course_id=t2.course_id
group by a.course_id,course_name
order by a.course_id

个人理解此类计算各种比率的问题都可以通过多重并表来完成