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

• 这个题主要是要理清楚他连接表格的逻辑，即谁在前谁在后，我在这个地方就想了很久，后面想出来了但是代码不够简洁，然后看了别人的题解茅塞顿开，看来代码还是不够熟练，自己在workbench上面敲得代码运行出来了，但是放在牛客网运行不出来，也找不到原因（哭唧唧），下面第一个代码是参考别人的简洁代码写出来的，真的很6啦，第二个代码是自己写的，在workbench上面敲得代码运行出来了，但是放在牛客网运行不出来。

select 
	a.course_id,
    c.course_name,
    round((count(distinct case when timestampdiff(minute,in_datetime,out_datetime)>=10 then a.user_id else null end)/count(distinct case when if_sign=1 then a.user_id else null end))*100,2) as 'attend_rate(%)'
from behavior_tb a
left join attend_tb b
on a.user_id=b.user_id and a.course_id=b.course_id
left join course_tb c
on a.course_id=c.course_id
group by a.course_id,c.course_name
order by a.course_id

with t as (select
	*,
	sum(if_sign) over (partition by course_id) as renhsu
from 
 behavior_tb
 where if_sign=1)
 
 select
	course_id,
    course_name,
    round((count(distinct user_id)/renhsu)*100,2) as 'attend_rate(%)'
from
(select 
	*,
    timestampdiff(minute,in_datetime,out_datetime) as diff
from
 (select
	user_id,
    t1.course_id,
in_datetime,
out_datetime,
course_name,
renhsu
from
(select
    t.user_id,
	t.course_id,
    in_datetime,
    out_datetime,
    renhsu
from  t 
left join attend_tb atb
on t.course_id=atb.course_id and t.user_id=atb.user_id)t1
left join  course_tb
using(course_id))t2
where  timestampdiff(minute,in_datetime,out_datetime)>=10)t3
group by course_id
order by course_id