Oracle中decode函数用法:
语法及含义：
用法1
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
用法2
decode(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3。当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多!
应用示例
场景：
有两张表：设备类型表，业务系统表。设备类型表存储的是设备资源标识和设备类型，业务系统表存储的是设备资源标识和所属的业务系统，两张表通过资源标识ci_id关联。
需求：
统计每种类型的设备资源应用于各个业务系统的数量。
结果：
统计结果应该如下图：
建表SQL：
-- Create table
create table TB_CI_ALL
(
CI_ID VARCHAR2(256),
CI_TYPE VARCHAR2(256)
)
-- Create table
create table TB_CI_BUSINESS
(
CI_ID VARCHAR2(256),
BIZ_TYPE VARCHAR2(256)
)
插入数据：
-- insert TB_CI_BUSINESS
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449216', '综合结算');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449216', '接口');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449216', '电子渠道');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449216', 'CRMOP');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('1111', 'BOMC');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4447376', 'BOMC');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4447377', 'BOMC');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449217', '综合结算');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449218', '综合结算');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449219', '综合结算');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449220', '综合结算');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449213', '综合结算');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449214', '综合结算');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449215', '综合结算');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449215', '接口');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449215', '电子渠道');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449215', 'CRMOP');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449217', '接口');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449217', '电子渠道');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449217', 'CRMOP');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449218', '接口');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449218', '电子渠道');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449218', 'CRMOP');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449219', '接口');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449219', '电子渠道');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449219', 'CRMOP');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449220', '接口');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449220', '电子渠道');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449220', 'CRMOP');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449213', '接口');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449213', '电子渠道');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449213', 'CRMOP');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449214', '接口');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449214', '电子渠道');
insert into TB_CI_BUSINESS (CI_ID, BIZ_TYPE)
values ('4449214', 'CRMOP');
commit;
--insert tb_ci_all
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('100', '小型机');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('101', '小型机');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('1111', '路由器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447376', '交换机');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447377', '交换机');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447378', '防火墙');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447379', '防火墙');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447380', '防火墙');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447381', '防火墙');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447382', '防火墙');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447383', '防火墙');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447384', '防火墙');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447385', '防火墙');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447386', '防火墙');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447387', '防火墙');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447388', '磁盘阵列');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447389', '磁盘阵列');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447390', '磁盘阵列');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447391', '磁盘阵列');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447392', '磁盘阵列');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447393', '磁盘阵列');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447394', '磁盘阵列');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447395', '磁盘阵列');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447396', '磁盘阵列');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447397', '磁盘阵列');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447497', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447498', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447499', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447500', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447501', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447502', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447503', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447504', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447505', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447506', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4447507', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4448854', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4448876', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4448877', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4448878', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4449213', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4449214', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4449215', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4449216', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4449217', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4449218', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4449219', 'PC服务器');
insert into TB_CI_ALL (CI_ID, CI_TYPE)
values ('4449220', 'PC服务器');
commit;
统计SQL如下：
select biz_type as BIZ_TYPE,
sum(decode(CI_TYPE, 'PC服务器', 1, 0)) as "PC服务器",
sum(decode(CI_TYPE, '小型机', 1, 0)) as "小型机",
sum(decode(CI_TYPE, '虚拟机', 1, 0)) as "虚拟机",
sum(decode(CI_TYPE, '磁盘阵列', 1, 0)) as "磁盘阵列",
sum(decode(CI_TYPE, '磁带库', 1, 0)) as "磁带库",
sum(decode(CI_TYPE, '路由器', 1, 0)) as "路由器",
sum(decode(CI_TYPE, '交换机', 1, 0)) as "交换机",
sum(decode(CI_TYPE, '负载均衡器', 1, 0)) as "负载均衡器",
sum(decode(CI_TYPE, '防火墙', 1, 0)) as "防火墙"
from (select biz.biz_type, a.ci_type
from tb_ci_business biz, tb_ci_all a
where biz.ci_id = a.ci_id(+)) t
group by biz_type;
查询结果：
