逐日实收分途径统计表

select 日期,所属分公司,所属管理站,收费方式,SUM(实收金额) as 实收金额

      from(select to_char(ANA_SF_DATE,'yyyy-mm-dd') 日期,

                  to_nchar(sf.ana_sf_fgs) 所属分公司,

                  to_nchar(sf.ana_sf_fwz) 所属管理站,

                  ANA_SF_SJJKTYPE 收费方式,

                  SUM(ANA_SF_SSXJ) 实收金额

                  from ana_sf sf

                  where ana_sf_date>=

                  to_date('2009-02-02','yyyy-mm-dd')

                  and ana_sf_date<

                  to_date('2012-02-02','yyyy-mm-dd')+1

                  and nvl(ana_sf_fwz_sf,1) like '%'

                  and nvl(ana_sf_fgs_sf,1) like '%'

                  and (Ana_sf_fyjlzt in('取消收费','已收费','抹帐') or ana_sf_fyxm in('购气','退气','部费'))

                  and (nvl(ana_sf_org_id,'00') like left('00',6)||'%' or

                  nvl(ana_sf_org_id_sf,'00') like

                  left ('00',6) ||'%')

               GROUP BY to_nchar(sf.ana_sf_fgs),

                  to_nchar(sf.ana_sf_fwz),

                  to_char(ANA_SF_DATE, 'yyyy-mm-dd'),

                  ANA_SF_SJJKTYPE

                            union all

                            select to_char(ana_zjzhmx_date,'yyyy-mm-dd') 日期,

                            ana_zjzhmx_fgs 所属公司,

                            ana_zjzhmx_fwz 所属管理站,

                             ANA_ZJZHMX_FKFS 收费方式,

                             sum((case 

                                     when ana_zjzhmx_sfflag='收' then

                                       1

                                       else

                                         -1

                                         end

                             )* ana_zjzhmx_je ) 实收金额

                             from ana_zjzhmx

                             where ANA_ZJZHMX_FLAG='预存款'

                             and abs(ana_zjzhmx_je)>0.0

                             and ana_zjzhmx_date<

                             to_date('2009-02-02','yyyy-mm-dd')

                             to_date('2009-02-02','yyyy-mm-dd')+1

                         AND nvl(ANA_ZJZHMX_FWZ_sf, 1) like '%'

           and nvl(ANA_ZJZHMX_FGS_sf, 1) like '%'

           AND (nvl(ANA_ZJZHMX_ORG_ID, '00') like

               left('00', 6) || '%' or

               nvl(ANA_ZJZHMX_ORG_ID_SF, '00') like

               left('00', 6) || '%')

         GROUP BY ana_zjzhmx_fgs,

                  ana_zjzhmx_fwz,

                  to_char(ANA_ZJZHMX_DATE, 'yyyy-mm-dd'),

                  ANA_ZJZHMX_FKFS)

 group by 所属分公司, 日期, 所属管理站, 收费方式

本文转自杨海龙的博客博客51CTO博客，原文链接http://blog.51cto.com/7218743/1441596如需转载请自行联系原作者

IT达仁