- case有两种格式,简单case函数和case搜索函数
case sex
when '1' then '男'
when '2' then '女'
else '未知' end
case when sex = '1' then '男'
when sex = '2' then '女'
else '未知' end
- 创建一张info表,其中包含id,name,tag三个字段
Sql> create table info(id int,name varchar(20),tag character(1));
Sql> insert into info(1,'Alice','2');
Sql> insert into info(2,'Jack','1');
Sql> insert into info(3,'Mary','0');
select id,name,(case tag when '1' then '男' when '2' then '女' else '未知' end ) sex from info;
id name sex
1 Alice 女
2 Jack 男
3 Mary 未知
- 例如SQL中常用到的case when 场景
case when value_tag='y' then '是' else '否' end value_tag
if(value_tag='y','是' ,'否') as value_tag
case when id between '0' and '99' then id else '-9' end
case when value_amt < 10000 then '101'
when value_amt between 10000 and 30000 then '102'
when value_amt > 30000 then '103' else '-1' end val_type