统计支付成功金额最大的前三十个供应商1-5w等支付笔数.sql
?
?
select a.payeename,
(select count(one_) from
?? (select l.payeename, case when (l.payamount >= 10000 and l.payamount < 50000) then 'one' else 'none' end as one_
????? from cmcs3_transfer_bill tb ,cmcs3_transfer_bill_list l where tb.transferbillid = l.transferbillid
??? and tb.stateid = 2 and l.bankstateid = 3
??? and tb.submittime >= to_date('2011-01-01', 'yyyy-MM-dd')
??? and tb.submittime < to_date('2012-01-01', 'yyyy-MM-dd')) x where x.one_ != 'none' and x.payeename = a.payeename
),
(select count(two_) from
?? (select l.payeename,case when (l.payamount >= 50000 and l.payamount < 500000) then 'two'? else 'none' end as two_
?????? from cmcs3_transfer_bill tb ,cmcs3_transfer_bill_list l where tb.transferbillid = l.transferbillid
??? and tb.stateid = 2 and l.bankstateid = 3
??? and tb.submittime >= to_date('2011-01-01', 'yyyy-MM-dd')
??? and tb.submittime < to_date('2012-01-01', 'yyyy-MM-dd')) x where x.two_ != 'none' and x.payeename = a.payeename
),
(select count(three_) from
?? (select l.payeename,case when (l.payamount >= 500000 and l.payamount < 1000000) then 'three' else 'none' end as three_
????? from cmcs3_transfer_bill tb ,cmcs3_transfer_bill_list l where tb.transferbillid = l.transferbillid
??? and tb.stateid = 2 and l.bankstateid = 3
??? and tb.submittime >= to_date('2011-01-01', 'yyyy-MM-dd')
??? and tb.submittime < to_date('2012-01-01', 'yyyy-MM-dd')) x where x.three_ != 'none' and x.payeename = a.payeename
),
(select count(four_) from
?? (select l.payeename,case when (l.payamount >= 1000000) then 'four' else 'none' end as four_
?????? from cmcs3_transfer_bill tb ,cmcs3_transfer_bill_list l where tb.transferbillid = l.transferbillid
??? and tb.stateid = 2 and l.bankstateid = 3
??? and tb.submittime >= to_date('2011-01-01', 'yyyy-MM-dd')
??? and tb.submittime < to_date('2012-01-01', 'yyyy-MM-dd')) x where x.four_ != 'none' and x.payeename = a.payeename
)
from (select l.payeename as payeename,
? case when (l.payamount >= 10000 and l.payamount < 50000) then 'one' else 'none' end as one_,
? case when (l.payamount >= 50000 and l.payamount < 500000) then 'two'? else 'none' end as two_,
? case when (l.payamount >= 500000 and l.payamount < 1000000) then 'three' else 'none' end as three_,
? case when (l.payamount >= 1000000) then 'four' else 'none' end as four_
??? from cmcs3_transfer_bill_list l where l.payeename in (select p.payeename from (
??? select tbl.payeename,sum(tbl.payamount) as totalAmount from cmcs3_transfer_bill tb ,
??? cmcs3_transfer_bill_list tbl
??? where tb.transferbillid = tbl.transferbillid
??? and tb.stateid = 2 and tbl.bankstateid = 3
??? and tb.submittime >= to_date('2011-01-01', 'yyyy-MM-dd')
??? and tb.submittime < to_date('2012-01-01', 'yyyy-MM-dd')
??? group by tbl.payeename order by totalAmount desc
? ) p where rownum <= 30)) a group by a.payeename;
?
?
