题解 | 最受欢迎的top3课程

SELECT 
    s.cid,
    t.pv,
    t.time as time_len
FROM
    (
        SELECT
            cid,
            AVG(score) AS avg_score
        FROM
            play_record_tb 
        GROUP BY
            cid
    ) s
    JOIN (
        SELECT
            c.cid,
            SUM(TIMESTAMPDIFF(minute, p.start_time, p.end_time)) AS time,
            COUNT(*) AS pv
        FROM
            play_record_tb p
            JOIN course_info_tb c ON c.cid = p.cid
        WHERE
            p.end_time <= c.release_date + INTERVAL 7 DAY
        GROUP BY
            c.cid
    ) t ON s.cid=t.cid
WHERE
    s.avg_score >= 3
ORDER BY
    t.pv DESC,
    t.time DESC
LIMIT 3

1. 平均数是总体的，观看数是一周内的，就分两个子查询来写
2. p.end_time <= c.release_date + INTERVAL 7 DAY，时间差可以直接用interval
3. 只要用了聚合函数就一定要group by，记得检查