select
A.trandate as 结算日期,
A.sm as 充值金额不大于1000的总金额,
A.co as 充值金额大于1000的总笔数,
B.sm as 充退金额不大于1000的总金额,
B.co as 充退金额大于1000的总笔数 from
(select a.trandate ,a.sm , b.co from
(
select trandate,sum(tranamt) as sm from mafedb.t_zjyw_tranrechis b where trancode ='2802' and transtat='00' and trandate>='20141001' and trandate<='20161130' and to_number(tranamt)<=1000 group by trandate
) a
full outer join
(select trandate,count(*) as co from mafedb.t_zjyw_tranrechis b where trancode ='2802' and transtat='00' and trandate>='20141001' and trandate<='20161130' and to_number(tranamt)>1000 group by trandate ) b
on a.trandate=b.trandate ) A
full outer join
(select a.trandate ,a.sm , b.co from
(
select trandate,sum(tranamt) as sm from mafedb.t_zjyw_tranrechis b where trancode ='2803' and transtat='00' and trandate>='20141001' and trandate<='20161130' and to_number(tranamt)<=1000 group by trandate
) a
full outer join
(select trandate,count(*) as co from mafedb.t_zjyw_tranrechis b where trancode ='2803' and transtat='00' and trandate>='20141001' and trandate<='20161130' and to_number(tranamt)>1000 group by trandate ) b
on a.trandate=b.trandate) B
on A.trandate=B.trandate order by A.trandate asc
