【sql:练习题14】查询各科成绩最高分、最低分和平均分
题目:查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
分析: 是按照各科查成绩 因此 是按照 courseid group by
我之前还不知道可以用 MAX(score) AS '最高分', MIN(score)AS '最低分' 这种写法
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 这种分段的 要引用一组关键字
case when then else end
于是sql 就写成这样:
SELECT courseid,MAX(score) AS '最高分', MIN(score)AS '最低分' ,AVG(score) AS '平均分' ,COUNT(*) AS '选修人数',
SUM(CASE WHEN student_score.score >= 60 THEN 1 ELSE 0 END)/COUNT(*) AS '及格率',
SUM(CASE WHEN student_score.score >= 70 AND student_score.score< 80 THEN 1 ELSE 0 END)/COUNT(*) AS '中等率',
SUM(CASE WHEN student_score.score >= 80 AND student_score.score<90 THEN 1 ELSE 0 END)/COUNT(*) AS '优良率',
SUM(CASE WHEN student_score.score >= 90 THEN 1 ELSE 0 END)/COUNT(*) AS '优秀率'
FROM student_score GROUP BY courseid ORDER BY COUNT(*)DESC,student_score.courseid ASC ;
运行:
优化:这里怎么再去关联student_course 表拿到课程名称呢?