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)
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 所属分公司, 日期, 所属管理站, 收费方式
本文转自杨海龙的博客博客51CTO博客，原文链接http://blog.51cto.com/7218743/1441596如需转载请自行联系原作者
IT达仁
