经典union的使用

一个用户下广告位  某一天有收入和支出  有支出不一定有收入  有收入不一定有支出  下例为按用户查询 sanhao 下的信息

支出如下：

收入如下：

按天进行查询，例如查询：

得到结果如下：

使用一般的按日期左关联，会出现错误。

正确的使用如下，把每个表的收入或者支出补全 为0，然后union合并 再进行分组合并

  select rownum rn,mm0.accesstime,nvl(mm1.inall,0) inall                                ,nvl(mm1.outall,0) outall,mm1.placeid                                ,decode(mm1.inall,null,0,mm1.inall)-decode(mm1.outall,null,0,mm1.outall) profit                                ,decode(inall,0,'-', to_char(round((decode(mm1.inall,null,0,mm1.inall)-decode(mm1.outall,null,0,mm1.outall) )/inall,4)*100)||'%') proRate                                                               from                         (                             select column_value as accesstime from table(fn_split('2013-10-12,2013-10-13,2013-10-14',',') )                         ) mm0,                         (                                                     select accesstime,placeid,sum(inall) inall,sum(outall2) outall  from                             (                                        --包广告位付款                                   select 0 inall,to_char(rd.accounttime,'yyyy-mm-dd') as accesstime,rd.placeid ,decode(sum(rd.paysum),null,0,sum(rd.paysum)) outall2                                   from ad_paidrecord rd                                   inner join ad_place pl on rd.placeid=pl.placeid and pl.ismonthly=1                                   where rd.placeid!=0--包广告位                                         and pl.ismonthly=1                                         and rd.rectype in(0,2)                                         and rd.accounttime>= to_date('2013-10-12','yyyy-mm-dd')                                         and rd.accounttime<= to_date('2013-10-14','yyyy-mm-dd')--根据记账日期（小的日期）                                         --and (i_placeid=-1 or rd.placeid=i_placeid)                                         and (rd.webuserid='sanhao' )                                   group by rd.accounttime,rd.placeid                                   union                                   --总收入                                                                 select sum(v.income)inall,to_char(v.accesstime,'yyyy-mm-dd') as accesstime,v.placeid,0                                   from v_placeincome v                                   where v.accesstime >= to_date('2013-10-12','yyyy-mm-dd')                                         and v.accesstime <=  to_date('2013-10-14','yyyy-mm-dd')                                       --  and (i_placeid=-1 or v.placeid=i_placeid)                                         and v.ismonthly=1                                         and (v.webuserid='sanhao')                                   group by accesstime,placeid                                 )                                 group by accesstime,placeid                          )  mm1                                              where  mm0.accesstime=mm1.accesstime(+)                           and (inall>0 or (nvl(mm1.outall,0))>0)                           order by placeid,mm0.accesstime asc;

 

转载于:https://www.cnblogs.com/riasky/p/3371985.html

相关资源：各显卡算力对照表！