统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

最新推荐文章于 2022-04-07 20:27:36 发布

世昌愿世昌盛

最新推荐文章于 2022-04-07 20:27:36 发布

阅读量1.1w

点赞数 4

本文链接：https://blog.csdn.net/Harden_zsc/article/details/108935822

版权

SELECT c.c_id 课程ID,c.c_name 课程NAME,
a.vv 不及格率,b.cc 中等率,c.ww 优良率,d.aa 优秀率
FROM score s,course c,
(SELECT a.cou/b.co vv FROM(SELECT c_id ,COUNT(c_id) cou FROM score WHERE s_score <60 GROUP BY c_id) a,(SELECT c_id ,COUNT(c_id) co FROM score GROUP BY c_id) b WHERE a.c_id=b.c_id) a,
(SELECT a.cou/b.co cc FROM(SELECT c_id ,COUNT(c_id) cou FROM score WHERE s_score >=60 AND s_score <70 GROUP BY c_id) a,(SELECT c_id ,COUNT(c_id) co FROM score GROUP BY c_id) b WHERE a.c_id=b.c_id) b,
(SELECT a.cou/b.co ww FROM(SELECT c_id ,COUNT(c_id) cou FROM score WHERE s_score >=70 AND s_score <85 GROUP BY c_id) a,(SELECT c_id ,COUNT(c_id) co FROM score GROUP BY c_id) b WHERE a.c_id=b.c_id) c,
(SELECT a.cou/b.co aa FROM(SELECT c_id ,COUNT(c_id) cou FROM score WHERE s_score >85 GROUP BY c_id) a,(SELECT c_id ,COUNT(c_id) co FROM score GROUP BY c_id) b WHERE a.c_id=b.c_id) d
WHERE s.c_id=c.c_id GROUP BY s.c_id