oracle中decode函数
oracle中decode函数一、语法
1、decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
2、DECODE(字段或字段的运算，值1，值2，值3）
该函数的含义如下：
(1)、当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
(2)、当然值1，值2，值3也可以是表达式
二、实例
1、创建一个学生成绩表。字段有： 学生名， 科目名和分数
create table student_score(
name varchar2(30),
subject varchar2(20),
score number(4,1)
);
2、插入测试数据
insert into student_score (name,subject,score)values('zhang san','Chinese',90);
insert into student_score (name,subject,score)values('zhang san','Mathematics',80);
insert into student_score (name,subject,score)values('zhang san','English',79);
3、实例、如果是中文课程的话， 显示分数， 其他课程分数为零。
select name,subject,decode(subject, 'Chinese',score,0) from student_score;
结果：
4、再次插入如下数据
insert into student_score (name,subject,score)values('li shi','Chinese',96);
insert into student_score (name,subject,score)values('li shi','Mathematics',86);
insert into student_score (name,subject,score)values('li shi','English',76);
insert into student_score (name,subject,score)values('wang wu','Chinese',92);
insert into student_score (name,subject,score)values('wang wu','Mathematics',82);
insert into student_score (name,subject,score)values('wang wu','English',72);
5、查询每个学生的各科成绩
select name,
sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Chinese",
sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Mathematics",
sum(decode(subject, 'English', nvl(score, 0), 0)) "English"
from student_score
group by name;
结果如图