Oracle数据库Decode()函数的使用方法

来源：互联网发布：淘宝咸鱼编辑：程序博客网时间：2024/05/17 20:09

[摘要] DECODE函数的作用：它可以将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。
[关键字] Oracle Decode()

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

　　区别于SQL的其它函数，DECODE函数还能识别和操作空值。

　　语法如下：

　　DECODE(control_value,value1,result1[,value2,result2…][,default_result]);

　　control _value

　　试图处理的数值。DECODE函数将该数值与后面的一系列的偶序相比较，以决定返回值。

　　value1

　　是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应

　　result1

　　是一组成序偶的结果值。

　　default_result 未能与任何一个值匹配时，函数返回的默认值。

　　示例如下：

　　select decode( x , 1 , ‘x is 1 ’, 2 , ‘x is 2 ’, ‘others’) from dual

　　当x等于1时，则返回‘x is 1’。

　　当x等于2时，则返回‘x is 2’。

　　否则，返回others’。

　　在需要比较2个值的时候，我们可以配合SIGN()函数一起使用。

　　SELECT DECODE( SIGN(5 -6), 1 'Is Positive', -1, 'Is Nagative', 'Is Zero')

　　同样，也可以用CASE实现：

　　SELECT CASE SIGN(5 - 6)

　　WHEN 1 THEN 'Is Positive'

　　WHEN　-1 THEN 'Is Nagative'

　　ELSE　'Is Zero' END

　　FROM DUAL

　　另外，大家还可以在Order by中使用Decode。

　　例：表table_subject，有subject_name列。要求按照：语、数、外的顺序进行排序。这时，就可以非常轻松的使用Decode完成要求了。

　　select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)

具体使用例子：

1、固定列数的行列转换如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
......
转换为
语文数学英语
student1 80 70 60
student2 90 80 100
......
语句如下：

select student,sum(decode(subject,'语文', grade,null))"语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student

2、不定列行列转换如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
......
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子

CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
--用于返回值
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;

例如有下表 tb
name              kecheng           chengji
张三       语文       98
张三       数学       78
张三       英语       99
李四       语文       85
李四       数学       72
李四       英语       80

SQL语句如下：
select
a.name as '姓名',
sum(case a.kecheng when '语文' then a.chengji else 0 end ) as '语文',
sum(case a.kecheng when '数学' then a.chengji else 0 end ) as '数学',
sum(case a.kecheng when '英语' then a.chengji else 0 end ) as '英语'
from tb as a
group by a.name

结果：
姓名      语文    数学      英语
李四      85 72 80
张三      98 78 99

有表如下：

select * from test026;

ID NAME SUBJECT SCORE

---------- -------------------- -------------------- ----------

1 jim 语文 88

1 jim 数学 84

1 jim 英语 90

2 kate 语文 86

2 kate 数学 76

2 kate 英语 96

想得到如下效果：

学生编号学生姓名语文数学英语

方法：

1.自连接：(这是自连接很典型的用处应当熟练掌握)

select a.id,a.name,a.score as "语文",b.score as "数学",c.score as "英语"

2 from test026 a,test026 b,test026 c

3 where a.id=b.id and a.subject='语文' and b.subject='数学'

4 and a.id=c.id and c.subject='英语';

ID NAME 语文数学英语

---------- -------------------- ---------- ---------- ----------

1 jim 88 84 90

2 kate 86 76 96

2 使用case when

select id,name,

sum(case when subject='语文' then score end) as "语文",

sum(case when subject='数学' then score end) as "数学",

sum(case when subject='英语' then score end) as "英语"

from test026

group by id,name

ID NAME 语文数学英语

---------- -------------------- ---------- ---------- ----------

1 jim 88 84 90

2 kate 86 76 96

3 decode

1 select max(id) as id,name,

2 max(decode(subject,'数学',score)) as "数学",

3 max(decode(subject,'语文',score)) as "语文",

4 max(decode(subject,'英语',score)) as "英语"

5 from test026

6* group by name

ID NAME 数学语文英语

---------- -------------------- ---------- ---------- ----------

1 jim 84 88 90

2 kate 76 86 96