Oracle中decode函数用法

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;

查询结果：