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

时间:2021-07-11 09:13:28
[摘要] 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
......
语句如下:

  1. select student,sum(decode(subject,'语文', grade,null))"语文",   
  2. sum(decode(subject,'数学', grade,null)) "数学",   
  3. sum(decode(subject,'英语', grade,null)) "英语"  
  4. from table  
  5. group by student  

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

  1. CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)   
  2. RETURN VARCHAR2   
  3. IS  
  4. --用于返回值   
  5. Col_c2 VARCHAR2(4000);   
  6. BEGIN  
  7. FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP   
  8. Col_c2 := Col_c2||cur.c2;   
  9. END LOOP;   
  10. Col_c2 := rtrim(Col_c2,1);   
  11. RETURN Col_c2;   
  12. 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