oracle的decode函数

一、decode函数的使用方法
decode()函数，它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。
语法结构如下

decode (expression, value1, result1, value2, result2, ..., valuen, resultn, default)

根据expression表达式进行判断，如果expression返回的值等于value1，则返回result1，如果expression返回的值等于等于value2，则返回result2，如果都不等于，则返回default值，如果没有定义default，则返回空值。

二、示例
创建表

create table student_score(
   name varchar2(30),
   subject varchar2(20),
   score number(4,1)
);

插入数据

insert into student_score (name,subject,score) values ('张三','中文',90);
insert into student_score (name,subject,score) values ('张三','数学',80);
insert into student_score (name,subject,score) values ('张三','外语',89);
insert into student_score (name,subject,score) values('李四','中文',96);
insert into student_score (name,subject,score) values('李四','数学',86);
insert into student_score (name,subject,score) values('李四','外语',76);
insert into student_score (name,subject,score) values('王五','中文',92);
insert into student_score (name,subject,score) values('王五','数学',82);
insert into student_score (name,subject,score) values('王五','外语',72);

查询

--如果学科为中文，则返回分数，否则返回0
select name,subject,decode(subject,'中文',score,0) from student_score;

--统计中文学科的分数
select name,sum(decode(subject,'中文',score,0)) as 中文 from student_score group by name;

--使用decode函数进行行转列
select name,
       sum(decode(subject, '中文', score, 0)) as 中文,
       sum(decode(subject, '数学', score, 0)) as 数学,
       sum(decode(subject, '外语', score, 0)) as 外语
  from student_score
 group by name;

--使用case when
select name,
       sum(case
             when subject = '中文' then
              nvl(score, 0)
             else
              0
           end) 中文,
       sum(case
             when subject = '数学' then
              nvl(score, 0)
             else
              0
           end) 数学,
       sum(case
             when subject = '外语' then
              nvl(score, 0)
             else
              0
           end) 外语
  from student_score
 group by name