SELECT c.c_id,c.c_name,MAX(sc.s_score) "最高分",MIN(sc.s_score) "最低分",AVG(sc.s_score) "平均分" ,((SELECT COUNT(s_id) FROM score WHERE s_score>=60 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) "及格率",((SELECT COUNT(s_id) FROM score WHERE s_score>=70 AND s_score<80 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) "中等率",((SELECT COUNT(s_id) FROM score WHERE s_score>=80 AND s_score<90 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) "优良率",((SELECT COUNT(s_id) FROM score WHERE s_score>=90 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) "优秀率"FROM course cLEFT JOIN score sc ON sc.c_id=c.c_id GROUP BY c.c_id
-- 18.查询各科成绩最高分 最低分和平均分:以如下形式显示:课程ID 课程name 最高分 最低分 平均分 及格率 中等率 优良率 优秀率 -- 及格为>=60 中等为:70-80 优良为:80-