oracle decode 函数技巧

在工作中经常遇到要用decoder函数
我这里举两个典型的例子

将表：
______________________________________
|a_name   |   a_subject   |  a_score |
|张三     |    语文       |   80     |
|张三     |    数学       |   70     |
|张三     |    英语       |   60     |
|李四     |    语文       |   90     |
|李四     |    数学       |   30     |
|李四     |    英语       |   100    |
--------------------------------------
转换成：
______________________________________
|   \  |  语文   |   数学   |   英语 |
|张三  |  80     |   70     |   60   |
|李四  |  90     |   30     |   100  |
--------------------------------------
--创建表
drop table t_across;
create table t_across(
a_name varchar2(20) not null,
a_subject varchar2(20) not null,
a_score number(3)
);
--插入数据
insert into t_across(a_name,a_subject,a_score)
values('张三','语文',80);
insert into t_across(a_name,a_subject,a_score)
values('张三','数学',70);
insert into t_across(a_name,a_subject,a_score)
values('张三','英语',60);
insert into t_across(a_name,a_subject,a_score)
values('李四','语文',90);
insert into t_across(a_name,a_subject,a_score)
values('李四','数学',30);


--显示数据
select * from t_across;
--显示交叉表的格式
select a_name "学生\成绩",sum(decode(a_subject,'语文',a_score,null)) "语文",  
                sum(decode(a_subject,'数学',a_score,null)) "数学",  
                sum(decode(a_subject,'英语',a_score,null)) "英语"  
from t_across
group by a_name;
----------
******************************************************
++++++++++++++++++++++++++++++++++++++++++++++++++++++
=======================================================
=======================================================
++++++++++++++++++++++++++++++++++++++++++++++++++++++
******************************************************



Oracle中Decode()函数使用技巧
DECODE函数是ORACLE PL/SQL是功能强大的函数之一。它有什么用途呢？ 先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。 如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。

　　decode()函数使用技巧

　　·软件环境:

　　1、Windows NT4.0+ORACLE 8.0.4

　　2、ORACLE安装路径为:C:\ORANT

　　·含义解释:

　　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

　　· 使用方法:

　　1、比较大小

　　select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

　　sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

　　例如:

　　变量1=10，变量2=20

　　则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

　






实力2：


建表：
create table T_SHOES
(
  P_ID    NUMBER(10),
  P_SIZE  NUMBER(3),
  P_COLOR VARCHAR2(6),
  P_NUM   NUMBER(5)
);
输入测试数据如下：
select p_id, p_size, p_color, p_num from t_shoes;  
P_ID P_SIZE P_COLOR P_NUM
1 38 黑 100
1 38 红 10
1 40 黑 50
2 38 黑 100
3 38 红 5 


因为结果集要通过存储过程,所以创建结果统计报表：
create table t_shoes_report
(
  P_SIZE  NUMBER(3),
  P_COLOR VARCHAR2(6),
  P_NUM   NUMBER(5)
);


创建视图,以提供所有颜色和尺寸的匹配情况(利用了笛卡尔乘积)  
create or replace view v_all_products as
select distinct all_size.p_size,all_color.p_color
from
(select distinct t.p_size from t_shoes t ) all_size,
(select distinct t.p_color from t_shoes t) all_color


通过逐步打印,完成以下存储过程的编写：
create or replace procedure  sp_report
  as
  str1 number(3);
  str2 varchar2(30000);
begin
   delete from t_shoes_report;
   for all_size in (select distinct p_size from v_all_products )
   loop  
      --dbms_output.put_line(all_size.p_size);  
      for all_color in ( select distinct p_color from v_all_products where p_size = all_size.p_size)  
      loop  
         --dbms_output.put_line(all_size.p_size||','||all_color.p_color);  
         --统计  
        select nvl(sum(t.p_num),0) into str1 from t_shoes t  
         where t.p_size = all_size.p_size  
         and t.p_color  = all_color.p_color;  
         --str2 := all_size.p_size||','||all_color.p_color||','||str1;  
         --dbms_output.put_line(str2);  
         insert into t_shoes_report(p_size,p_color,p_num)  
         values(all_size.p_size,all_color.p_color,str1);  
      end loop;  
   end loop;    
   commit;      
end ;   

测试：    
SQL> execute sp_report;   

PL/SQL procedure successfully completed    
SQL> select * from t_shoes_report;    
P_SIZE P_COLOR  P_NUM    表A
------ ------- ------    
    38 黑         200    
    38 红          15    
    40 黑          50    
    40 红           0   


在得到上面的数据,可以通过以下方法进行报表显示    
报表    
尺寸/颜色 黑 红
38 200 15
40 50 0

1、通过java程序将表A数据转化HTML代码,显示为表B    
2、通过SQL语句转化    
select    
   t.p_size 尺寸,  
   sum(decode(t.p_color,'黑',t.p_num)) 黑,  
   sum(decode(t.p_color,'红',t.p_num)) 红    
from t_shoes_report t  
group by t.p_size  
    
结果：    
尺寸 黑 红
38 200 15
40 50 0



对于特殊sql来说还挺多的！下次再总结了！