统计支付宝交易sql

最新推荐文章于 2024-11-13 10:59:25 发布

hunuanqing

最新推荐文章于 2024-11-13 10:59:25 发布

阅读量366

点赞数

分类专栏： sql 文章标签： sql

本文链接：https://blog.csdn.net/hunuanqing/article/details/72900672

版权

sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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