1. case when 表达式有两种形式
复制代码 代码如下:
–简单case函数
case sex
when ‘1’ then ‘男’
when ‘2’ then ‘女’
else ‘其他’ end
–case搜索函数
case
when sex = ‘1’ then ‘男’
when sex = ‘2’ then ‘女’
else ‘其他’ end
2. case when 在语句中不同位置的用法
2.1 select case when 用法
复制代码 代码如下:
select grade, count (case when sex = 1 then 1 /*sex 1为男生，2位女生*/
else null
end) 男生数,
count (case when sex = 2 then 1
else null
end) 女生数
from students group by grade;
2.2 where case when 用法
复制代码 代码如下:
select t2.*, t1.*
from t1, t2
where (case when t2.compare_type = ‘a’ and
t1.some_type like ‘nothing%’
then 1
when t2.compare_type != ‘a’ and
t1.some_type not like ‘nothing%’
then 1
else 0
end) = 1
2.3 group by case when 用法
复制代码 代码如下:
select
case when salary <= 500 then ‘1’
when salary > 500 and salary <= 600 then ‘2’
when salary > 600 and salary <= 800 then ‘3’
when salary > 800 and salary <= 1000 then ‘4’
else null end salary_class, — 别名命名
count(*)
from table_a
group by
case when salary <= 500 then ‘1’
when salary > 500 and salary <= 600 then ‘2’
when salary > 600 and salary <= 800 then ‘3’
when salary > 800 and salary <= 1000 then ‘4’
else null end;
3.关于if-then-else的其他实现
3.1 decode() 函数
复制代码 代码如下:
select decode(sex, ‘m’, ‘male’, ‘f’, ‘female’, ‘unknown’)
from employees;
貌似只有oracle提供该函数，而且不支持ansi sql，语法上也没case when清晰，个人不推荐使用。
3.2 在where中特殊实现
复制代码 代码如下:
select t2.*, t1.*
from t1, t2
where (t2.compare_type = ‘a’ and t1.some_type like ‘nothing%’)
or
(t2.compare_type != ‘a’ and t1.some_type not like ‘nothing%’)
这种方法也是在特殊情况下使用，要多注意逻辑，不要弄错。