MySQL中统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比，运用2种实现方法

# 查询课程id，课程表名称
select c.CId,Cname,
# 计算各分数段人数 方法1
# sum(case when s.score<=100 and s.score>85 then 1 else 0 end) as "[100-85]",
# sum(case when s.score<=85 and s.score>70 then 1 else 0 end) as "[85-70]",
# sum(case when s.score<=70 and s.score>60 then 1 else 0 end) as "[70-60]",
# sum(case when s.score<=60 and s.score>0 then 1 else 0 end) as "[60-0]",

# 计算各分数段人数 方法2
count(if(s.score <=100 and s.score >85,1,null))as "[100-86]",
count(if(s.score <=85 and s.score >70,1,null))as "[85-71]",
count(if(s.score <=70 and s.score >60,1,null))as "[70-60]",
count(if(s.score <=60 and s.score >0,1,null))as "[60-0]",


# 计算百分比,通过round，将小数点保留1位  方法1
# round(sum(case when s.score <= 100 and s.score > 85 then 1 else 0 end) * 100.0 / count(*),1) '[100-85]_percent',
# round(sum(case when s.score <= 85 and s.score > 70 then 1 else 0 end) * 100.0 / count(*),1) as '[85-70]_percent',
# round(sum(case when s.score <= 70 and s.score >= 60 then 1 else 0 end) * 100.0 / count(*),1) as '[70-60]_percent',
# round(sum(case when s.score <= 60 and s.score > 0 then 1 else 0 end) * 100.0 / count(*),1) as '[60-0]_percent'
# count(if(birth_date <= '1980-01-01',null,1)) 'old'

# 计算百分比，通过round ，将小数点保留1位，方法2
round(sum(if(s.score <=100 and s.score >85,1,null))*100 / count(*),1) '[100-86]_percent',
round(sum(if(s.score <=85 and s.score >70,1,null))*100 / count(*),1) '[85-71]_percent',
round(sum(if(s.score <=70 and s.score >60,1,null))*100 / count(*),1) '[70-60]_percent',
round(sum(if(s.score <60 and s.score >0,1,null))* 100 / count(*),1) '[60-0]_percent'
# 用到了2张表，一张课程表，一张成绩表
from
      SC s join Course c on s.CId=c.CId
      group by c.CId,Cname;
# 验真
select * from SC s join Course c on s.CId=c.CId where score>0 and score<=60;