物流公司统计按物资类别采购的前二十家sql

2、集团主要的供应商（按物资分类列举前10或20家名单），年采购金额、占比，结算方式，付款周期；（夏）

年份要求是2013年

arap_djfb中的单据日期不是常规的日期类型

需要做这样的转换才可以

select to_char(to_date(billdate,'yyyy,mm,dd'),'yyyy' )from arap_djfb  

按照材料类别的分类需要截取字段的后两位

select  areaclcode,substr(bd_areacl.areaclcode,-2), substr(bd_areacl.areaclcode,length(bd_areacl.areaclcode)-1,2) from bd_areacl

感谢影哥的-2，我搞复杂了

sql如下

select --arap_djzb.djbh 单据编号,

       --arap_djfb.billdate as 单据日期,

       --bd_corp.unitname as 付款单位,

      -- arap_djfb.zy as 付款摘要,

       sum(arap_djfb.bbye) as allmoney ,

       --arap_djfb.fkyhmc as 付款银行名称,

       --(select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) 付款银行账号,

       --bd_cubasdoc.custcode as 收款单位编码,

       --bd_cubasdoc.custname as 收款单位名称,

       bd_accbank.unitname 收款单位名称,

       --bd_accbank.bankname as 收款银行名称,

       --bd_accbank.bankacc as 收款银行账号,

       bd_areacl.areaclcode,

       bd_areacl.areaclname

      -- arap_djfb.payflag

  from bd_cubasdoc, arap_djfb, bd_cumandoc, arap_djzb, bd_corp, bd_accbank,bd_areacl

 where arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc

   and bd_cumandoc.pk_cubasdoc = bd_cubasdoc.pk_cubasdoc

   and arap_djfb.vouchid = arap_djzb.vouchid

   and bd_corp.pk_corp = arap_djzb.dwbm

   and bd_accbank.pk_accbank = arap_djfb.skyhzh

   and bd_areacl.pk_areacl=bd_cubasdoc.pk_areacl

   and arap_djfb.payflag in ('', '')

   and arap_djfb.dr = ''

   --and (select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) ='32001735038059899999'

   --and bd_corp.unitcode='010201'

    and substr(arap_djfb.billdate,1,4)=''

    and bd_areacl.areaclcode<>''

    and length(bd_accbank.unitname)>3

    and bd_accbank.unitname not like '江苏省建工%'

   --and bd_areacl.areaclname like '%沧州%'

  -- and bd_cubasdoc.custcode = '17051211693'

  group by  bd_accbank.unitname,  bd_areacl.areaclcode, bd_areacl.areaclname

order by substr(bd_areacl.areaclcode,-2), allmoney desc

昨天晚上想到oracle应该可以分组取前20，于是百度，果然找到了itpub

http://www.itpub.net/thread-1290416-1-1.html

row_number()over(partition by substr(areaclcode,-2) order by allmoney desc) c1 这里c1不可以直接放在where条件，必须再嵌套一次

dense_rank()over也行

 select * from (

    select allmoney,unitname,areaclcode,areaclname,

     row_number()over(partition by substr(areaclcode,-2) order by allmoney desc) c1 from

        (select --arap_djzb.djbh 单据编号,

       --arap_djfb.billdate as 单据日期,

       --bd_corp.unitname as 付款单位,

      -- arap_djfb.zy as 付款摘要,

       sum(arap_djfb.bbye) as allmoney ,

       --arap_djfb.fkyhmc as 付款银行名称,

       --(select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) 付款银行账号,

       --bd_cubasdoc.custcode as 收款单位编码,

       --bd_cubasdoc.custname as 收款单位名称,

       bd_accbank.unitname ,

       --bd_accbank.bankname as 收款银行名称,

       --bd_accbank.bankacc as 收款银行账号,

       bd_areacl.areaclcode,

       bd_areacl.areaclname

      -- arap_djfb.payflag

  from bd_cubasdoc, arap_djfb, bd_cumandoc, arap_djzb, bd_corp, bd_accbank,bd_areacl

 where arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc

   and bd_cumandoc.pk_cubasdoc = bd_cubasdoc.pk_cubasdoc

   and arap_djfb.vouchid = arap_djzb.vouchid

   and bd_corp.pk_corp = arap_djzb.dwbm

   and bd_accbank.pk_accbank = arap_djfb.skyhzh

   and bd_areacl.pk_areacl=bd_cubasdoc.pk_areacl

   and arap_djfb.payflag in ('', '')

   and arap_djfb.dr = ''

   --and (select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) ='32001735038059899999'

   --and bd_corp.unitcode='010201'

    and substr(arap_djfb.billdate,1,4)=''

    and bd_areacl.areaclcode<>''

    and length(bd_accbank.unitname)>3

    and bd_accbank.unitname not like '江苏省建工%'

   --and bd_areacl.areaclname like '%沧州%'

  -- and bd_cubasdoc.custcode = '17051211693'

  group by  bd_accbank.unitname,  bd_areacl.areaclcode, bd_areacl.areaclname

order by substr(bd_areacl.areaclcode,-2), allmoney desc))

where c1<=20