统计支付成功金额最大的前三十个供应商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;