前面的 sql 语句写错了,应该是下面这样。create table rpt_sixprodchain2 as
select 200609 sdate, callno,
sum(case when pay1 is null then 0 else pay1 end ) pay1 ,
sum(case when pay2 is null then 0 else pay2 end) pay2 ,
sum(case when pay3 is null then 0 else pay3 end) pay3 ,
sum(case when pay4 is null then 0 else pay4 end) pay4 ,
sum(case when pay5 is null then 0 else pay5 end) pay5 ,
sum(case when pay6 is null then 0 else pay6 end) pay6
from rpt_sixprodchain_tmp group by callno
select 200609 sdate, callno,
sum(case when pay1 is null then 0 else pay1 end ) pay1 ,
sum(case when pay2 is null then 0 else pay2 end) pay2 ,
sum(case when pay3 is null then 0 else pay3 end) pay3 ,
sum(case when pay4 is null then 0 else pay4 end) pay4 ,
sum(case when pay5 is null then 0 else pay5 end) pay5 ,
sum(case when pay6 is null then 0 else pay6 end) pay6
from rpt_sixprodchain_tmp group by callno
create table rpt_sixprodchain2 as
select 200609 sdate, callno,
sum(nvl(pay1,0)) pay1 ,
sum(nvl(pay2,0)) pay2 ,
sum(nvl(pay3,0)) pay3 ,
sum(nvl(pay4,0)) pay4 ,
sum(nvl(pay5,0)) pay5 ,
sum(nvl(pay6,0)) pay6
from rpt_sixprodchain_tmp group by callno
整理一下,再发一遍:有表:sixprodchain_tmp ,字段 和 示例 数据如下:Call no pay1 pay2 pay3 pay4 pay5 pay613888888888 263.47 0.00 0.00 0.00 0.00 0.00
13888888888 0.00 660.00 0.00 0.00 0.00 0.00
13888888888 0.00 0.00 194.00 0.00 0.00 0.00
13888888888 0.00 0.00 0.00 103.00 0.00 0.00
13888888888 0.00 0.00 0.00 0.00 0.00 9.70该表有大约 1000 万条记录。对该表根据 Callno 字段进行 Group ,并对各个 Pay 字段进行 sum,语句如下:create table rpt_sixprodchain2 as
select 200609 sdate, callno,
sum(case when pay1 is null then 0 else pay1 end ) pay1 ,
sum(case when pay2 is null then 0 else pay2 end) pay2 ,
sum(case when pay3 is null then 0 else pay3 end) pay3 ,
sum(case when pay4 is null then 0 else pay4 end) pay4 ,
sum(case when pay5 is null then 0 else pay5 end) pay5 ,
sum(case when pay6 is null then 0 else pay6 end) pay6
from rpt_sixprodchain_tmp group by callno执行完毕之后,rpt_sixprodchain2 表中有大月 200 万条记录,但是其中有 50000 条左右的记录统计结果不正确,如 13888888888:13888888888 0 0 0 0 0 9.7正确的应该是:13888888888 263.47 660.00 194.00 103.00 0 9.7执行了几次,每次 rpt_sixprodchain2 表中都有 50000 条左右的记录不正确,而且每次不正确的手机号码也都不全一样。
---------------------------------------你把case when去掉,直接试试:
create table rpt_sixprodchain2 as
select 200609 sdate, callno,
sum(pay1) pay1 ,
sum(pay2) pay2 ,
sum(...) pay3 ,
sum(...) pay4 ,
sum(...) pay5 ,
sum(...) pay6
from rpt_sixprodchain_tmp group by callno
---------------------------------------再试试如下sql:
create table rpt_sixprodchain2 as
select 200609 sdate, callno,
sum(decode(pay1,null,0,pay1)) pay1 ,
sum(decode(pay2,null,0,pay2)) pay2 ,
sum(...) pay3 ,
sum(...) pay4 ,
sum(...) pay5 ,
sum(...) pay6
from rpt_sixprodchain_tmp group by callno