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

诡异了并不难的题花了比较多的时间……因为在workbench里可以但这里显示不行，所以重新写~脑壳痛

select a.course_id,course_name,
	   round((count(case when 在线时长 >= 10 and 在线时长 is not null and 是否签到 = 1 then user_id end)/count(case when 是否签到 = 1 then user_id end))*100,2) as 考勤率
       from
(select behavior_tb.user_id,behavior_tb.course_id,max(if_sign) as 是否签到,sum(timestampdiff(minute,in_datetime,out_datetime)) as 在线时长 
	   from 
       behavior_tb left join attend_tb on behavior_tb.user_id = attend_tb.user_id and behavior_tb.course_id = attend_tb.course_id
       group by user_id,course_id) as a
       join course_tb on a.course_id = course_tb.course_id
       group by a.course_id,course_name
       order by course_id;