sum(casewhen score >=85then1else0end) [85-100],
sum(casewhen score >=70and score <85then1else0end) [70-85],
sum(casewhen score >=60and score <70then1else0end) [60-70],
sum(casewhen score <60then1else0end) [0-60]
from sc , Course
where SC.Cid = Course.Cid
groupby Course.Cid , Course.Cname
orderby Course.Cid
--纵向显示1(显示存在的分数段)
select m.Cid [课程编号] , m.Cname [课程名称] , 分数段= (
casewhenn.score >=85then'85-100'
when n.score >=70and n.score <85then'70-85'
when n.score >=60and n.score <70then'60-70'
else'0-60'
end) ,
count(1) 数量
from Course m , sc n
where m.Cid = n.Cid
groupby m.Cid , m.Cname , (
casewhenn.score >=85then'85-100'
when n.score >=70and n.score <85then'70-85'
when n.score >=60and n.score <70then'60-70'
else'0-60'
end)
orderby m.Cid , m.Cname , 分数段
--纵向显示2(显示存在的分数段，不存在的分数段用0显示)
select m.Cid [课程编号] , m.Cname [课程名称] , 分数段= (
casewhenn.score >=85then'85-100'
when n.score >=70and n.score <85then'70-85'
when n.score >=60and n.score <70then'60-70'
else'0-60'
end) ,
count(1) 数量
from Course m , sc n
where m.Cid = n.Cid
groupbyall m.Cid , m.Cname , (
casewhenn.score >=85then'85-100'
when n.score >=70and n.score <85then'70-85'
when n.score >=60and n.score <70then'60-70'
else'0-60'
end)
orderby m.Cid , m.Cname , 分数段?
--23.2 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[<60]及所占百分比
--横向显示
select m.Cid 课程编号, m.Cname 课程名称,
(selectcount(1) from SC where Cid = m.Cid and score <60) [0-60],
cast((selectcount(1) from SC where Cid = m.Cid and score <60)*100.0/ (selectcount(1) from SC where Cid = m.Cid) asdecimal(18,2)) [百分比(%)],
(selectcount(1) from SC where Cid = m.Cid and score >=60and score <70) [60-70],
cast((selectcount(1) from SC where Cid = m.Cid and score >=60and score <70)*100.0/ (selectcount(1) from SC where Cid = m.Cid) asdecimal(18,2)) [百分比(%)],
(selectcount(1) from SC where Cid = m.Cid and score >=70and score <85) [70-85],
cast((selectcount(1) from SC where Cid = m.Cid and score >=70and score <85)*100.0/ (selectcount(1) from SC where Cid = m.Cid) asdecimal(18,2)) [百分比(%)],
(selectcount(1) from SC where Cid = m.Cid and score >=85) [85-100],
cast((selectcount(1) from SC where Cid = m.Cid and score >=85)*100.0/ (selectcount(1) from SC where Cid = m.Cid) asdecimal(18,2)) [百分比(%)]
from Course m
orderby m.Cid
--纵向显示1(显示存在的分数段)
select m.Cid [课程编号] , m.Cname [课程名称] , 分数段= (
casewhenn.score >=85then'85-100'
when n.score >=70and n.score <85then'70-85'
when n.score >=60and n.score <70then'60-70'
else'0-60'
end) ,
count(1) 数量 ,
cast(count(1) *100.0/ (selectcount(1) from sc where Cid = m.Cid) asdecimal(18,2)) [百分比(%)]
from Course m , sc n
where m.Cid = n.Cid
groupby m.Cid , m.Cname , (
casewhenn.score >=85then'85-100'
when n.score >=70and n.score <85then'70-85'
when n.score >=60and n.score <70then'60-70'
else'0-60'
end)
orderby m.Cid , m.Cname , 分数段
--纵向显示2(显示存在的分数段，不存在的分数段用0显示)
select m.Cid [课程编号] , m.Cname [课程名称] , 分数段= (
casewhenn.score >=85then'85-100'
when n.score >=70and n.score <85then'70-85'
when n.score >=60and n.score <70then'60-70'
else'0-60'
end) ,
count(1) 数量 ,
cast(count(1) *100.0/ (selectcount(1) from sc where Cid = m.Cid) asdecimal(18,2)) [百分比(%)]
from Course m , sc n
where m.Cid = n.Cid
groupbyall m.Cid , m.Cname , (
casewhenn.score >=85then'85-100'
when n.score >=70and n.score <85then'70-85'
when n.score >=60and n.score <70then'60-70'
else'0-60'
end)
orderby m.Cid , m.Cname , 分数段?
--24、查询学生平均成绩及其名次
思路：所有学生的平均成绩（一个记录集合），再使