Oracle之case when

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;

​