-- 商户统计查询
select
ccu.id,ccu.currencyname,css.id,css.supplierno,css.suppliername,
sum(cb.PAYAMOUNT)-sum(cb.COMAMT)-sum(cb.TELEFEE) 人民币交易额,
Min(cb.sendtime) 购汇开始日期,
Max(cb.sendtime) 购汇结束日期,
count(cbsuss.id) 购汇成功笔数,
nvl(sum(cbsuss.FOREIGNAMOUNT),0) 购汇成功金额,
count(cbun.id) 购汇待确认笔数,
nvl(sum(cbun.FOREIGNAMOUNT),0) 购汇待确认金额,
count(cbfail.id) 购汇失败笔数,
nvl(sum(cbfail.FOREIGNAMOUNT),0) 购汇失败金额,
count(cb.id) 购汇总笔数,
nvl(sum(cb.FOREIGNAMOUNT),0) 购汇总金额,
Min(cp.sendtime) 付汇开始日期,
Max(cp.sendtime) 付汇结束日期,
count(cpsuss.id) 付汇成功笔数,
nvl(sum(cpsuss.AMOUNT),0) 付汇成功金额,
count(cpun.id) 付汇待确认笔数,
nvl(sum(cpun.AMOUNT),0) 付汇待确认金额,
count(cpfail.id) 付汇失败笔数,
nvl(sum(cpfail.AMOUNT),0) 付汇失败金额,
count(cp.id) 付汇总笔数,
nvl(sum(cp.AMOUNT),0) 付汇总金额,
count(cbsuss.id)-count(cpsuss.id) 成功差异笔数,
nvl(sum(cbsuss.FOREIGNAMOUNT) -sum(cpsuss.AMOUNT),0) 成功差异金额
from cbe_buyfxinstraction cb,
cbe_paymentinstruction cp,
cbe_suppliermerchantacct csc,
cbe_suppliersetting css,
cbe_currencysetting ccu,
(select id, FOREIGNAMOUNT from cbe_buyfxinstraction where status = 3) cbsuss,
(select id, FOREIGNAMOUNT from cbe_buyfxinstraction where status = 4) cbfail,
(select id, FOREIGNAMOUNT from cbe_buyfxinstraction where status = 2) cbun,
(select id, AMOUNT from cbe_paymentinstruction where status = 3) cpsuss,
(select id, AMOUNT from cbe_paymentinstruction where status = 4) cpfail,
(select id, AMOUNT from cbe_paymentinstruction where status = 2) cpun
where cb.id = csc.buyfeinstractionid
and cp.id = csc.paymentinstractionid
and css.id = cb.supplierid
and ccu.id = cb.buycurrencyid
and ccu.id=cp.currencyid
and cb.id = cbsuss.id(+)
and cb.id = cbfail.id(+)
and cb.id = cbun.id(+)
and cp.id = cpsuss.id(+)
and cp.id = cpfail.id(+)
and cp.id = cpun.id(+)
and cb.status in(2,3,4)
group by ccu.id,ccu.currencyname,css.id,css.supplierno,css.suppliername;
select
ccu.id,ccu.currencyname,css.id,css.supplierno,css.suppliername,
sum(cb.PAYAMOUNT)-sum(cb.COMAMT)-sum(cb.TELEFEE) 人民币交易额,
Min(cb.sendtime) 购汇开始日期,
Max(cb.sendtime) 购汇结束日期,
count(cbsuss.id) 购汇成功笔数,
nvl(sum(cbsuss.FOREIGNAMOUNT),0) 购汇成功金额,
count(cbun.id) 购汇待确认笔数,
nvl(sum(cbun.FOREIGNAMOUNT),0) 购汇待确认金额,
count(cbfail.id) 购汇失败笔数,
nvl(sum(cbfail.FOREIGNAMOUNT),0) 购汇失败金额,
count(cb.id) 购汇总笔数,
nvl(sum(cb.FOREIGNAMOUNT),0) 购汇总金额,
Min(cp.sendtime) 付汇开始日期,
Max(cp.sendtime) 付汇结束日期,
count(cpsuss.id) 付汇成功笔数,
nvl(sum(cpsuss.AMOUNT),0) 付汇成功金额,
count(cpun.id) 付汇待确认笔数,
nvl(sum(cpun.AMOUNT),0) 付汇待确认金额,
count(cpfail.id) 付汇失败笔数,
nvl(sum(cpfail.AMOUNT),0) 付汇失败金额,
count(cp.id) 付汇总笔数,
nvl(sum(cp.AMOUNT),0) 付汇总金额,
count(cbsuss.id)-count(cpsuss.id) 成功差异笔数,
nvl(sum(cbsuss.FOREIGNAMOUNT) -sum(cpsuss.AMOUNT),0) 成功差异金额
from cbe_buyfxinstraction cb,
cbe_paymentinstruction cp,
cbe_suppliermerchantacct csc,
cbe_suppliersetting css,
cbe_currencysetting ccu,
(select id, FOREIGNAMOUNT from cbe_buyfxinstraction where status = 3) cbsuss,
(select id, FOREIGNAMOUNT from cbe_buyfxinstraction where status = 4) cbfail,
(select id, FOREIGNAMOUNT from cbe_buyfxinstraction where status = 2) cbun,
(select id, AMOUNT from cbe_paymentinstruction where status = 3) cpsuss,
(select id, AMOUNT from cbe_paymentinstruction where status = 4) cpfail,
(select id, AMOUNT from cbe_paymentinstruction where status = 2) cpun
where cb.id = csc.buyfeinstractionid
and cp.id = csc.paymentinstractionid
and css.id = cb.supplierid
and ccu.id = cb.buycurrencyid
and ccu.id=cp.currencyid
and cb.id = cbsuss.id(+)
and cb.id = cbfail.id(+)
and cb.id = cbun.id(+)
and cp.id = cpsuss.id(+)
and cp.id = cpfail.id(+)
and cp.id = cpun.id(+)
and cb.status in(2,3,4)
group by ccu.id,ccu.currencyname,css.id,css.supplierno,css.suppliername;