SQL面试50题-18

查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)

SELECT s.c_id as "课程ID",c.c_name as "课程name"
,MAX(s.s_score) AS "最高分"
,MIN(s.s_score) AS "最低分"
,ROUND(AVG(s.s_score),2) AS "平均分"
,ROUND(SUM(CASE WHEN s.s_score >= 60 THEN 1.0 ELSE 0.0 END)/COUNT(s.c_id),2) AS "及格率"
,ROUND(SUM(CASE WHEN s.s_score >70 AND s.s_score <= 80 THEN 1.0 ELSE 0.0 END)/COUNT(s.c_id),2)  AS "中等率"
,ROUND(SUM(CASE WHEN s.s_score > 80 AND s.s_score <= 90 THEN 1.0 ELSE 0.0 END)/COUNT(s.c_id),2) AS "优良率"
,ROUND(SUM(CASE WHEN s.s_score >= 90 THEN 1.0 ELSE 0.0 END)/COUNT(s.c_id),2) AS "优秀率"
FROM Score s INNER JOIN Course c ON c.c_id = s.c_id  
GROUP BY s.c_id,c.c_name

注意的问题：1、SELECT子句中的字段名与GROUP BY 要一致
2. 70< < 80 这种写法是错误的
3.在postgreSQL中 CASE WHEN 语句中的1要写成1.0(不然算出来的及格率是0）
4.可以用ROUND函数来起到一个四舍五入的效果