题解 | 最受欢迎的top3课程

select p.cid,count(*) as pv,sum(timestampdiff(minute,p.start_time,p.end_time)) as time_len
from play_record_tb as p
left join course_info_tb as c
on p.cid = c.cid
where datediff(start_time, release_date) < 7
group by p.cid
having avg(p.score) >= 3
order by pv desc,time_len desc
limit 3;

需要记住的两点：

1，计算时间长度：timestampdiff(minute,p.start_time,p.end_time)

2，计算时间间隔：datediff(start_time, release_date) < 7