Oracle中decode怎么用

小編給大家分享一下Oracle中decode怎么用，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

語法結構如下：
decode (expression, sch_1, res_1)
decode (expression, sch_1, res_1, sch_2, res_2)
decode (expression, sch_1, res_1, sch_2, res_2, ...., sch_n, res_n)
decode (expression, sch_1, res_1, default)
decode (expression, sch_1, res_1, sch_2, res_2, default)
decode (expression, sch_1, res_1, sch_2, res_2, ...., sch_n, res_n, default)
比較表達式和搜索字，如果匹配，返回結果；如果不匹配，返回default值；如果未定義default值，則返回空值。

select name,sub,decode(sub, 'chinese',score,0) from student_score;
decode函數(shù)的功能在于它不但可以判斷常值，同樣可以判斷字段.以上語句:
當科目為chinese時，選擇score字段對應的值，而非chinese科目，其對應的score值都為0

select name,
sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Ch",
sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Math",
sum(decode(subject, 'English', nvl(score, 0), 0)) "En"
from xxx group by name;   ---行轉(zhuǎn)列

decode函數(shù)同樣可以當做條件使用，如： where score= decode(subject, 'Chinese',score)

等同于以下case when

select name,
sum(case when subject='Ch'
then nvl(score,0)
else 0
end) "Ch",
sum(case when subject='Math'
then nvl(score,0)
else 0
end) "Math",
sum(case when subject='En'
then nvl(score,0)
else 0
end) "En"
from xxx group by name;

看完了這篇文章，相信你對“Oracle中decode怎么用”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業(yè)資訊頻道，感謝各位的閱讀！