ORACLE-DECODE函数的常见用法-CSDN博客

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

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

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

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

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改写，可以将VP和CEO放到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.这种情况，其实就是通过decode将fields重新归类，将原有的类别合并成新的类别，在归类结果的基础上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的每个值都转变为对应的数字，那么按照此数字排序不就可以了吗？所以，很快想到了DECODE或CASE。使用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升序排列。