其基本語法為：
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 =
CASE WHEN THEN
WHEN THEN
ELSE 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可用於=,>=,