《SQL面试50题》刷题笔记 day11（ 知识点：case when[conditions] then result else result end）

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

解法1：

select c.cid, c.cname,
sum(case when sc.sscore >=85 then 1 else 0 end)　as num1,
sum(case when sc.sscore >=70 and sc.sscore<85 then 1 else 0 end)　as num2,
sum(case when sc.sscore >=60 and sc.sscore <70 then 1 else 0 end) as num3,
sum(case when sc.sscore <60 then 1 else 0 end) as num_4,
sum(case when sc.sscore >=85 then 1 else 0 end)/sum(case when sc.sscore then 1 end) as p1,
sum(case when sc.sscore >=70 and sc.sscore<85 then 1 else 0 end)/sum(case when sc.sscore then 1 end) as p2,
sum(case when sc.sscore >=60 and sc.sscore <70 then 1 else 0 end)/sum(case when sc.sscore then 1 end) as p3,
sum(case when sc.sscore <60 then 1 else 0 end)/sum(case when sc.sscore then 1 end) as p4
from score sc join course c
on sc.cid=c.cid group by sc.cid;

解法2：

select sc.cid, c.cname,
sum(case when sc.sscore >=85 then 1 else 0 end) 'num[100-85]',
sum(case when sc.sscore <85 and sc.sscore>=70 then 1 else 0 end) 'num[85-70]',
sum(case when sc.sscore <70 and sc.sscore>=60 then 1 else 0 end) 'num[70-60]',
sum(case when sc.sscore <60 then 1 else 0 end) 'num[60-0]',
concat(round(sum(case when sc.sscore >=85 then 1 else 0 end)/count(sc.sscore)*100,2),'%') as 'p[100-85]',
concat(round(sum(case when sc.sscore <85 and sc.sscore>=70 then 1 else 0 end)/count(sc.sscore)*100,2),'%') as 'p[85-70]',
concat(round(sum(case when sc.sscore <70 and sc.sscore>=60 then 1 else 0 end)/count(sc.sscore)*100,2),'%') as 'p[70-60]',
concat(round(sum(case when sc.sscore <60 then 1 else 0 end)/count(sc.sscore)*100,2),'%') as 'p[60-0]'
from score sc
join course c
on sc.cid= c.cid
group by sc.cid;

运行结果：

问题18 查询每门课程被选修的学生数

select cid,count(sid) total
from score
group by cid;