ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE-DECODE函数的常见用法

ORACLE-DECODE函数的常见用法

原创 Linux操作系统 作者:l476861438 时间:2012-03-19 09:48:10 0 删除 编辑

摘自<剑破冰山ORACLE开发艺术> --仅做学习使用

DECODE函数的常见用途: 按字段内容分组、按字段内容排序、固定行转列

1)       使用DECODE实现按字段内容分组

有时候分组可能比较复杂, 比如需要对某些字段内容合并分组处理, 这样使用简单的GROUP BY就不行了,那么使用CASEDECODE就可以获得预期的结果,如:

Eg:

--建立简单的雇员及对应职位表

drop table rl_test;

create table rl_test

(

id number(10),

name varchar2(10),

title varchar2(20)

);

begin

insert into rl_test values(1, 'jack', 'VP');

insert into rl_test values(2, 'tony', 'CEO');

insert into rl_test values(3, 'merry', 'VP');

insert into rl_test values(4, 'james', 'OPERATION');

insert into rl_test values(5, 'linda', 'OPERATION');

end;

Screen shot:

 

需求: 现在需要统计VP及以上职位的人数、普通雇员的人数。对于这个要求, 使用简单的GROUP BUY title是不行的,使用DECODE来实现就很简单了。

Code

select decode(title, 'VP', 'CEO_VP', 'CEO', 'CEO_VP', 'OPERATION') title,

count(1) title_cnt

from rl_test

group by decode(title, 'VP', 'CEO_VP', 'CEO', 'CEO_VP', 'OPERATION');

Screen shot

 

 Case表达式也可以解决这个问题, 下面用Case表达式改写上面的DECODE:

--使用Simple CASE改写, CASE比较复杂的时候, 可以把CASE语句放在括号内,提高可读--性,不这样也是可以的。

select (case title

        when 'VP' then 'VP_CEO'

        when 'CEO' then 'VP_CEO'

        else 'OPERATION'

        end) title,

        count(1)

from rl_test

group by (case title

          when 'VP' then 'VP_CEO'

          when 'CEO' then 'VP_CEO'

          else 'OPERATION'

          end);

--使用Search CASE改写, 可以将VPCEO放到IN

select (case

        when title in ('VP','CEO') then 'VP_CEO'

        else 'OPERATION'

        end) title,

        count(1)

from rl_test

group by (case

          when title in ('VP','CEO') then 'VP_CEO'

          else 'OPERATION'

          end);

注:

1. simple case 无法匹配NULL值,searched case可以。在大部分情况下, 还是DECODE用起来比较简单。

2.这种情况, 其实就是通过decodefields重新归类,将原有的类别合并成新的类别,在归类结果的基础上group

2 使用DECODE实现按字段内容排序

在日常的开发中可能遇到这种情况,比如一个表有ID,NAME字段(ID为代理主键),需要按NAME值指定排序规则,当然在大部分情况下,这种排序应该在表设计的时候就增加额外排序键,这样就可以把按NAME值排序转换为按排序键排序了。但是有时候设计没有考虑到这点,那么怎么实现这种排序呢?一般有两种可能碰到的情况:按字段内容排序和按字段内容指定动态排序,下面分别介绍这两种情况的解决方法。

--建立简单的部门表,ID是序列,DEPT_NAME是部门名,REGION_ID是部门所属区域编号,假设每个部门分布在很多区域,有分公司之类的

drop table rl_test;

create table rl_test

(

id number,

dept_name varchar2(10),

region_id number(10)

);

begin

insert into rl_test values(1,'DEPT A',12);

insert into rl_test values(2,'DEPT A',10);

insert into rl_test values(3,'DEPT A',9);

insert into rl_test values(4,'DEPT A',7);

insert into rl_test values(5,'DEPT B',12);

insert into rl_test values(6,'DEPT B',13);

insert into rl_test values(7,'DEPT B',22);

insert into rl_test values(8,'DEPT B',9);

insert into rl_test values(9,'DEPT C',8);

insert into rl_test values(10,'DEPT C',10);

insert into rl_test values(11,'DEPT C',11);

commit;

end;

Screen shot:

①按字段内容排序

现在的需求是:按"DEPT A"" DEPT B"" DEPT C"的顺序排列,对于每个部门内部按区REGION_ID升序排列。

分析:这里的部门名DEPT_NAME不是数字,直接排序是不行的,试想一下如果能将DEPT_NAME的每个值都转变为对应的数字,那么按照此数字排序不就可以了吗?所以,很快想到了DECODECASE。使用DECODE解决,CASE类似:

Code:

select id,dept_name,region_id

from rl_test

order by DECODE(dept_name, 'DEPT A',1,'DEPT B',2,'DEPT C',3),region_id;

Screen shot:

从上面结果看出,分别按 "DEPT A""DEPT B""DEPT C"排列,每个部门内按REGION_ID升序排列,完成这个排序功能主要就是ORDER BY DECODE 的使用。

注:

1.       总结:到此可以看出,实现按字段内容分组是使用decode把需要需要并入相同组的fields更换成同一个new_field_name,然后group by new_field_name;实现按字段内容排序主要是使用decode将不可直接排序的列名替换成可以排序的数字,实现排序功能。

2.       疑问:我在做测试的时候直接ORDER BY DEPT_NAME,并没有使用DECODE(..,..,..)也成功实现了排序,不是特别清楚问什么原文中说不可以直接ORDER BY DEPT_NAME的原因。

原文中的DEPT_NAME的值是"部门 A ""部门B""部门C",自己在作测试的时候也尝试了这种情况,同样不用DECODE也可以正确排序,具体的原因需要进一步思考。这里先简单标注下。

②按字段内容指定动态列排序

现在把上面的需求改为:若DEPT_NAME"DEPT A",则按ID升序排列,否则按REGION_ID升序排列,要保证像①一样的部门先后规则。先来分析这种动态列排序,主要包括两个排序要求:Ⅰ、部门先后规则,即分别按 "DEPT A""DEPT B""DEPT C"排列。Ⅱ、若DEPT_NAME"DEPT A",则按ID升序排列,否则按REGION_ID升序排列。

实现这个排序:Ⅰ我们已经实现。现在主要考虑Ⅱ,使用DECODE可以很容易写出如下SQL实现:

select id,dept_name,region_id

from rl_test

order by decode(dept_name,'DEPT A',id,region_id);

Screen shot:

这时候发现,DEPT_NAME并不是按照"DEPT A""DEPT B""DEPT C"排列,之后加上①的ORDER BY语句即可实现满足需求的SQL.

Code

select id,dept_name,region_id

from rl_test

order by decode(dept_name,'DEPT A',1,'DEPT B',2,'DEPT C',3),

decode(dept_name,'DEPT A',id,region_id);

Screen shot

注:ORDER BY 不同子句有先后优先级之分,如果将上面的SQL写成了:

select id,dept_name,region_id

from rl_test

order by

decode(dept_name,'DEPT A',id,region_id),

decode(dept_name,'DEPT A',1,'DEPT B',2,'DEPT C',3);

则结果如下:

Screen shot:

DECODE函数的使用灵活性很强,恰当的使用可以实现很多复杂的需求。

3)使用DECODE实现行转列

关于这个知识点,我已经做了总结,包括固定行转列和动态行专列。具体参考:

http://space.itpub.net/24025515/viewspace-718736

这里主要说明为什么需要MAX,因为要实现行转列,按字段分组,对DECODE中的非分组列必须要有组函数,当然MINSUMAVG等分组函数也可以实现,MAXMIN对任何类型都适用,SUMAVG只能对数值型适用,所以常使用MAXMIN

 

1.jpg

2.jpg

3.jpg

5.jpg

6.jpg

7.jpg

4.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24025515/viewspace-718942/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle_行列互转
请登录后发表评论 登录
全部评论

注册时间:2012-03-15

  • 博文量
    8
  • 访问量
    13396