case when
1、语法
case
when 条件表达式1 then value1
when 条件表达式2 then value2
...
else 默认值
end
2、语义
如果表达式成立,那么整个语句值为表达式 then 后面的值,如果所有表达式都不成立,会取 else 后面的默认值,else 语句可以省略
select * from student;
-- 统计男、女生人数
select ssex, count(*) from student group by ssex;
-- 使用case when统计
select sum(case when ssex = '男' then 1 else 0 end) 男生人数,
sum(case when ssex = '男' then 0 else 1 end) 女生人数
from student s;
-- 统计列印各科成绩,各分数段人数:课程id,课程名称,[100-85],(85-70],(70-60],[ <60]
select s.cno, c.cname,
sum(case when s.score >= 85 and s.score <= 100 then 1 else 0 end) "[100-85]",
sum(case when s.score >= 70 and s.score < 85 then 1 else 0 end) "(85-70]",
sum(case when s.score >= 60 and s.score < 70 then 1 else 0 end) "(70-60]",
sum(case when s.score < 60 then 1 else 0 end) "[ <60]"
from sc s join course c on s.cno = c.cno
group by s.cno, c.cname;