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函数的功能在于它不但可以判断常值，同样可以判断字段.以上语句:
当科目为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;   ---行转列

decode函数同样可以当做条件使用，如： 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怎么用”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！