场景描述：

实验要求是求平均分top3的班级各科均分情况，实验手册中原sql是

WITH class_subject_avg_score AS(

    SELECT class_no,

                   subject_name,

                   avg(score) AS subject_avg_score

    FROM school_subject_scores GROUP BY class_no, subject_name

), top_class_no AS (

    SELECT class_no

    FROM class_subject_avg_score

    GROUP BY class_no

    ORDER BY sum(subject_avg_score) DESC LIMIT 3

) SELECT class_no,

                 subject_name,

                 subject_avg_score

  FROM class_subject_avg_score

WHERE class_no IN ( SELECT class_no FROM top_class_no) ORDER BY class_no, subject_name;

建议方案：

上述sql中，top_class_no 部分获取平均分前3的班级是直接把class_subject_avg_score中各科目的平均分相加，若班级中存在学生只有部分科目有成绩，按此种实现方式，算出来的平均分前3并不准确，建议修改sql为：

WITH class_subject_avg_score AS(

    SELECT class_no,

                   subject_name,

                   avg(score) AS subject_avg_score

    FROM school_subject_scores GROUP BY class_no, subject_name

), top_class_no AS (

    SELECT class_no

    FROM school_subject_scores

    GROUP BY class_no

    ORDER BY avg(score) DESC LIMIT 3

) SELECT class_no,

                 subject_name,

                 subject_avg_score

  FROM class_subject_avg_score

WHERE class_no IN ( SELECT class_no FROM top_class_no) ORDER BY class_no, subject_name;