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