查询各科成绩最高分、最低分和平均分

最新推荐文章于 2023-05-06 00:21:03 发布

By_MoS

最新推荐文章于 2023-05-06 00:21:03 发布

阅读量7.3w

点赞数 10

本文链接：https://blog.csdn.net/qq_21853321/article/details/108946911

版权

数据库

建表语句点击详见

– 以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90


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 c
LEFT JOIN score sc ON sc.c_id=c.c_id 
GROUP BY c.c_id