初始数据
|
select zjjx, sum(cgsl), sum(cgjeh), sum(sbsl), sum(sbjeh) from ( select zjjx,count(zjje) cgsl,sum(zjje) cgjeh,0 sbsl,0 sbjeh from yw_zjfpjl where substr(to_char(zjsj, 'yyyy-mm-dd hh24:mi:ss'), 0, 7) = '2016-10' group by zjjx union all select zjjx,0 cgsl,0 cgjeh,count(zjje) sbsl,sum(zjje) sbjeh from yw_fjcwjl where substr(to_char(zjsj, 'yyyy-mm-dd hh24:mi:ss'), 0, 7) = '2016-10' group by zjjx ) group by zjjx;
--结果如下：
/* 1 一等奖 1 200 0 0 2 三等奖 16 160 5 50 3 四等奖 28 140 23 115 4 五等奖 52 104 33 66 */
其实，yw_zjfpjl 是发票摇奖成功记录表，而该表中并没有摇奖失败数据(0 sbsl, 0 sbjeh)，添加这些子虚乌有的数据 只是为了使两个结果集的结构完整，同理 yw_fjcwjl 表中的 0 cgsl,0 cgjeh也是如此。
因为仅仅是两个结果集的拼接，所以采用 union all |
--1.0 示例表 YW_ZJFPJL(发票摇奖成功记录表) 、 YW_FJCWJL(发票摇奖失败记录表)
|
select * from (select sum(case when zjjx = '一等奖' then sum(zjje) else 0 end) as 一等奖成功金额, sum(case when zjjx = '二等奖' then sum(zjje) else 0 end) as 二等奖成功金额, sum(case when zjjx = '三等奖' then sum(zjje) else 0 end) as 三等奖成功金额, sum(case when zjjx = '四等奖' then sum(zjje) else 0 end) as 四等奖成功金额, sum(case when zjjx = '五等奖' then sum(zjje) else 0 end) as 五等奖成功金额 from yw_zjfpjl group by zjjx) , (select sum(case when zjjx = '一等奖' then sum(zjje) else 0 end) as 一等奖失败金额, sum(case when zjjx = '二等奖' then sum(zjje) else 0 end) as 二等奖失败金额, sum(case when zjjx = '三等奖' then sum(zjje) else 0 end) as 三等奖失败金额, sum(case when zjjx = '四等奖' then sum(zjje) else 0 end) as 四等奖失败金额, sum(case when zjjx = '五等奖' then sum(zjje) else 0 end) as 五等奖失败金额 from yw_fjcwjl group by zjjx);
--结果如下：
因为此处是把第二个结果集(发票摇奖失败金额)拼接到 第一个结果集后面(两行数据变成一行数据。因此原先结果集的5个字段变为10个字段)，因此两个结果集的连接方式为 ‘, ’
因为表中 内层 sum 就是为了统计 各个奖项的金额，外层sum 就是再对内层结果集细分 |
--2.0 按奖项分组，汇总各个奖项的数量
|
select * from (select sum(case when zjjx = '一等奖' then count(zjje) else 0 end) as 一等奖成功数量, sum(case when zjjx = '二等奖' then count(zjje) else 0 end) as 二等奖成功数量, sum(case when zjjx = '三等奖' then count(zjje) else 0 end) as 三等奖成功数量, sum(case when zjjx = '四等奖' then count(zjje) else 0 end) as 四等奖成功数量, sum(case when zjjx = '五等奖' then count(zjje) else 0 end) as 五等奖成功数量 from yw_zjfpjl group by zjjx), (select sum(case when zjjx = '一等奖' then count(zjje) else 0 end) as 一等奖失败数量, sum(case when zjjx = '二等奖' then count(zjje) else 0 end) as 二等奖失败数量, sum(case when zjjx = '三等奖' then count(zjje) else 0 end) as 三等奖失败数量, sum(case when zjjx = '四等奖' then count(zjje) else 0 end) as 四等奖失败数量, sum(case when zjjx = '五等奖' then count(zjje) else 0 end) as 五等奖失败数量 from yw_fjcwjl group by zjjx);
--结果如下： --1 0 16 28 52 0 0 5 23 33 |
--3.0 按奖项分组，汇总各个奖项的金额
|
select * from (select sum(case when zjjx = '一等奖' then sum(zjje) else 0 end) as 一等奖成功金额, sum(case when zjjx = '二等奖' then sum(zjje) else 0 end) as 二等奖成功金额, sum(case when zjjx = '三等奖' then sum(zjje) else 0 end) as 三等奖成功金额, sum(case when zjjx = '四等奖' then sum(zjje) else 0 end) as 四等奖成功金额, sum(case when zjjx = '五等奖' then sum(zjje) else 0 end) as 五等奖成功金额 from yw_zjfpjl group by zjjx), (select sum(case when zjjx = '一等奖' then sum(zjje) else 0 end) as 一等奖失败金额, sum(case when zjjx = '二等奖' then sum(zjje) else 0 end) as 二等奖失败金额, sum(case when zjjx = '三等奖' then sum(zjje) else 0 end) as 三等奖失败金额, sum(case when zjjx = '四等奖' then sum(zjje) else 0 end) as 四等奖失败金额, sum(case when zjjx = '五等奖' then sum(zjje) else 0 end) as 五等奖失败金额 from yw_fjcwjl group by zjjx);
--结果如下：
--1 200 0 160 140 104 0 0 50 115 66
|
--4.0 按奖项分组，汇总各个奖项的数量、金额
|
select * from (select sum(case when zjjx = '一等奖' then count(zjje) else 0 end) as 一等奖成功数量, sum(case when zjjx = '二等奖' then count(zjje) else 0 end) as 二等奖成功数量, sum(case when zjjx = '三等奖' then count(zjje) else 0 end) as 三等奖成功数量, sum(case when zjjx = '四等奖' then count(zjje) else 0 end) as 四等奖成功数量, sum(case when zjjx = '五等奖' then count(zjje) else 0 end) as 五等奖成功数量 from yw_zjfpjl group by zjjx), (select sum(case when zjjx = '一等奖' then count(zjje) else 0 end) as 一等奖失败数量, sum(case when zjjx = '二等奖' then count(zjje) else 0 end) as 二等奖失败数量, sum(case when zjjx = '三等奖' then count(zjje) else 0 end) as 三等奖失败数量, sum(case when zjjx = '四等奖' then count(zjje) else 0 end) as 四等奖失败数量, sum(case when zjjx = '五等奖' then count(zjje) else 0 end) as 五等奖失败数量 from yw_fjcwjl group by zjjx) union all from (select sum(case when zjjx = '一等奖' then sum(zjje) else 0 end) as 一等奖成功金额, sum(case when zjjx = '二等奖' then sum(zjje) else 0 end) as 二等奖成功金额, sum(case when zjjx = '三等奖' then sum(zjje) else 0 end) as 三等奖成功金额, sum(case when zjjx = '四等奖' then sum(zjje) else 0 end) as 四等奖成功金额, sum(case when zjjx = '五等奖' then sum(zjje) else 0 end) as 五等奖成功金额 from yw_zjfpjl group by zjjx), (select sum(case when zjjx = '一等奖' then sum(zjje) else 0 end) as 一等奖失败金额, sum(case when zjjx = '二等奖' then sum(zjje) else 0 end) as 二等奖失败金额, sum(case when zjjx = '三等奖' then sum(zjje) else 0 end) as 三等奖失败金额, sum(case when zjjx = '四等奖' then sum(zjje) else 0 end) as 四等奖失败金额, sum(case when zjjx = '五等奖' then sum(zjje) else 0 end) as 五等奖失败金额 from yw_fjcwjl group by zjjx);
--结果如下：
/* 1 1 0 16 28 52 0 0 5 23 33 2 200 0 160 140 104 0 0 50 115 66 */
|
--5.0 示例表 xxdzmx (第三方发票摇奖记录表，其中cgbz =0成功中奖，cgbz=1 非成功中奖)
6.0 case when 经典用法---(按奖项分组，汇总各个奖项的数量、金额)
|
select * from (select 1 num, '奖项金额', sum(case when djmc= '一等奖' and cgbz=0 then djje else 0 end) 一等奖成功, sum(case when djmc= '二等奖' and cgbz=0 then djje else 0 end) 二等奖成功, sum(case when djmc= '三等奖' and cgbz=0 then djje else 0 end) 三等奖成功, sum(case when djmc= '四等奖' and cgbz=0 then djje else 0 end) 四等奖成功, sum(case when djmc= '五等奖' and cgbz=0 then djje else 0 end) 五等奖成功, sum(case when djmc= '一等奖' and cgbz=1 then djje else 0 end) 一等奖失败, sum(case when djmc= '二等奖' and cgbz=1 then djje else 0 end) 二等奖失败, sum(case when djmc= '三等奖' and cgbz=1 then djje else 0 end) 三等奖失败, sum(case when djmc= '四等奖' and cgbz=1 then djje else 0 end) 四等奖失败, sum(case when djmc= '五等奖' and cgbz=1 then djje else 0 end) 五等奖失败 from xxdzmx t where dzyf=20111129 and zflx=0 union all select 2 num, '奖项数量', sum(case when djmc= '一等奖' and cgbz=0 then 1 else 0 end) 一等奖成功, sum(case when djmc= '二等奖' and cgbz=0 then 1 else 0 end) 二等奖成功, sum(case when djmc= '三等奖' and cgbz=0 then 1 else 0 end) 三等奖成功, sum(case when djmc= '四等奖' and cgbz=0 then 1 else 0 end) 四等奖成功, sum(case when djmc= '五等奖' and cgbz=0 then 1 else 0 end) 五等奖成功, sum(case when djmc= '一等奖' and cgbz=1 then 1 else 0 end) 一等奖失败, sum(case when djmc= '二等奖' and cgbz=1 then 1 else 0 end) 二等奖失败, sum(case when djmc= '三等奖' and cgbz=1 then 1 else 0 end) 三等奖失败, sum(case when djmc= '四等奖' and cgbz=1 then 1 else 0 end) 四等奖失败, sum(case when djmc= '五等奖' and cgbz=1 then 1 else 0 end) 五等奖失败 from xxdzmx t where dzyf=20111129 and zflx=0 ) order by num;
--结果如下： -- 1 奖项金额 0 50 10 0 2 200 0 10 5 2 -- 2 奖项数量 0 1 1 0 1 1 0 1 1 1
|
