select A.c_no, A.c_name,
sum(case when A.s_score>=85 then 1 else 0 end) as '85-100',
sum(case when A.s_score>=70 and A.s_score<85 then 1 else 0 end) as '70-85',
sum(case when A.s_score>=60 and A.s_score<70 then 1 else 0 end) as '60-70',
sum(case when A.s_score<60 then 1 else 0 end) as '0-60'
from (select test.score.c_no,test.course.C_name,test.score.S_no,test.score.s_score from test.score,test.course where test.score.c_no=test.course.c_no) as A
group by A.c_no
12-23
631
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
04-11
871
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)