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') and ana_zjzhmx_date< 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 所属分公司, 日期, 所属管理站, 收费方式