一,DECODE函数
其基本语法为:
Sql代码
- DECODE(value, if1, then1, if2, then2, if3, then3,...ifn, thenn, else)
表示如果value等于if1时,DECODE函数的结果返回then1,...,如果不等于任何一个if值,则返回else。亦即:decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
延伸用法:
1. 与sign函数联用比较大小:
Sql代码
- select decode(sign(arg1-arg2),-1, arg1, arg2) from dual; --get arg1与arg2的较小值
Sql代码
- select decode(sign(3-5),1 ,3, 5) from dual
注:sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1
2. 表、视图结构转化:
基本思路:使用substrb函数实现对字段的判断,然后用decode函数对数据进行重新计算,并生成新的数据和构成新的表(table or view)。
二,CASE WHEN
其语法如下:
Sql代码
- SELECT <myColumnSpec> =
- CASE WHEN <A> THEN <somethingA>
- WHEN <B> THEN <somethingB>
- ELSE <somethingE> END
除了可以在select 中使用CASE 外,where 子句,group by 子句,order by 子句都可以使用
Sql代码
- SELECT CASE
- WHEN price IS NULL THEN 'Unpriced'
- WHEN price < 10 THEN 'Bargain'
- WHEN price BETWEEN 10 and 20 THEN 'Average'
- ELSE 'Gift to impress relatives'
- END AS "Range",
- Title
- FROM titles
- where
- CASE
- WHEN price IS NULL THEN 'Unpriced'
- WHEN price < 10 THEN 'Bargain'
- WHEN price BETWEEN 10 and 20 THEN 'Average'
- ELSE 'Gift to impress relatives' END in('Average','Bargain')
- GROUP BY CASE
- WHEN price IS NULL THEN 'Unpriced'
- WHEN price < 10 THEN 'Bargain'
- WHEN price BETWEEN 10 and 20 THEN 'Average'
- ELSE 'Gift to impress relatives' END,
- Title
- ORDER BY CASE
- WHEN price IS NULL THEN 'Unpriced'
- WHEN price < 10 THEN 'Bargain'
- WHEN price BETWEEN 10 and 20 THEN 'Average'
- ELSE 'Gift to impress relatives'
- END,Title
rm_site_master 表结构:
rma_center | name
---------------------------
123 |qw
23 |ASde
45 |sssdf
55 |e3fbg
55555 |adfv
22221 |sdfsfe
4 |sdfeg
579 |lojgdex
Sql代码
- select name,
- CASE
- WHEN rma_center IS NULL THEN 'Null'
- WHEN rma_center > 1000 THEN '>1000'
- WHEN rma_center BETWEEN 30 and 100 THEN '30~100'
- ELSE 'Gift to impress relatives' END AS "RMA CENTER Type"
- from rm_site_master
- where CASE
- WHEN rma_center IS NULL THEN 'Null'
- WHEN rma_center > 1000 THEN '>1000'
- WHEN rma_center BETWEEN 30 and 100 THEN '30~100'
- ELSE 'Gift to impress relatives' END in('30~100','>1000')
- group by CASE
- WHEN rma_center IS NULL THEN 'Null'
- WHEN rma_center > 1000 THEN '>1000'
- WHEN rma_center BETWEEN 30 and 100 THEN '30~100'
- ELSE 'Gift to impress relatives' END,
- name
- order by CASE
- WHEN rma_center IS NULL THEN 'Null'
- WHEN rma_center > 1000 THEN '>1000'
- WHEN rma_center BETWEEN 30 and 100 THEN '30~100'
- ELSE 'Gift to impress relatives' END,
- name
三,DECODE 与CASE WHEN 的比较
1,DECODE Oracle 特有;
2,CASE WHEN Oracle , SQL Server, MySQL 都可用;
3,DECODE 只能用做相等判断,但是可以配合sign函数进行大于,小于,等于的判断,CASE 可用于=,>=,<,<=,<>,is null,is not null 等的判断;
4,DECODE 使用其来比较简洁,CASE 虽然复杂但更为灵活;