统计每个导师带的所有学生的每个科目的平均/最高/最低/总成绩,并按平均成绩排序,并且只要平均成绩大于60的记录
select result.*,te.tea_name from
(
select st.teacher_id,sc.score_subject,avg(sc.score_score) avg_score,max(sc.score_score),min(sc.score_score),sum(sc.score_score)
from t_score sc inner join t_student st on sc.student_id = st.stu_id
GROUP BY st.teacher_id,sc.score_subject
) result /* 把()里的内容当成一张表 */
LEFT JOIN t_teacher te ON te.tea_id = result.teacher_id
ORDER BY result.avg_score ASC /* 按升序排序*/ /* 顺序错误,在where下边 */
where result.avg_score > 60
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘where result.avg_score > 90’ at line 15
[err]1064-您的SQL语法有错误;请查看与您的MySQL服务器版本相对应的手册,了解在第15行“where result.avg_score>90”附近使用的正确语法。
改正:先写: where result.avg_score > 60
后写: ORDER BY result.avg_score ASC