Oracle数据库中decode函数的使用

一、decode函数的使用方法：

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

decode (expression, search_1, result_1)decode (expression, search_1, result_1, search_2, result_2)decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)decode (expression, search_1, result_1, default)decode (expression, search_1, result_1, search_2, result_2, default)decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回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('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);

查询：

select a.*,rowid from student_score a;

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

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

select name,score as 中文 from student_score;

---使用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 whenselect 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