- 统计一下每个course_id的观看次数及总观看时长用来计算平均观看时长
select course_id,
round(sum(timestampdiff(minute,in_datetime,out_datetime))/ count(course_id),2) as avg_len
from attend_tb
group by course_id
- 将这个表单和course_tb合并获取course_name并按照avg_len降序
select course_name, avg_len
from (
select course_id,
round(sum(timestampdiff(minute,in_datetime,out_datetime))/ count(course_id),2) as avg_len
from attend_tb
group by course_id
) temp
inner join course_tb
using(course_id)
order by avg_len desc