oracle的decode和case的用法
接上例子:
decode:
select SNO,NAME,SEX from student;
转化前:
SNO | NAME | SEX |
51871011 | xxx | 1 |
51871012 | yyy | 2 |
51871013 | ooo |
1.1:select SNO,NAME, decode(sex,1,'男生',2,‘女生’,3,‘女生’) as SEX from student;
输出结果:
SNO | NAME | SEX |
51871011 | xxx | 男生 |
51871012 | yyy | 女生 |
51871013 | ooo | 其他 |
性别为空显示“无”,不为空时候正常输出还需注意一点:
1.2:select SNO,decode(SEX,NULL,'无',SEX ) as SEX from student;
输出结果:
SNO | NAME | SEX |
51871011 | xxx | 1 |
51871012 | yyy | 2 |
51871013 | ooo | 无 |
case:
以下如上decode的输出结果: 1.1,1.2所结合
case when SEX = 1 then ‘男生’
when SEX = 2 then ‘女生’
when SEX = 3 then ‘其他’
when SEX is null then ‘无’
else 'unknow'
end ) SEX