初始数据

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);

--结果如下：

200 0 160 140 104 0 0 50 115 66

因为此处是把第二个结果集(发票摇奖失败金额)拼接到 第一个结果集后面(两行数据变成一行数据。因此原先结果集的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

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 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

 

转载于:https://www.cnblogs.com/iyoume2008/p/6126330.html

相关资源：C# WPF 写的摇奖转盘