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;

查询结果:

查询结果

赞(0)
未经允许禁止转载:优米格 » Oracle中decode函数用法

评论 抢沙发

合作&反馈&投稿

商务合作、问题反馈、投稿,欢迎联系

广告合作侵权联系