物流公司统计按物资类别采购的前二十家sql
2024-08-21 09:14:29
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
最新文章
- Java基础学习总结——Java对象的序列化和反序列化
- mysql索引的一些知识
- flv转mp4选项设置
- 玲珑杯1007-A 八进制大数加法(实现逻辑陷阱与题目套路)
- chrome调试、移动端调试
- Delphi捕捉DLL执行所抛出的异常。
- 错误记录--关于foreach,集合已修改;可能无法执行枚举操作
- ubuntu linux 13.04更新
- BitSort
- php小测试,难点与分享
- sessionStorage、localStorage 存储及如何存储数组与对象
- 老凡原创PHPcms发布上架chinaz
- 二维码开源库ZBar-windows下编译和使用
- 【STM32H7教程】第10章 STM32H7的FLASH,RAM和栈使用情况(map和htm文件)
- USSD 杂记
- AndroidTV端的requestFocus()问题
- Hadoop 和 MPP 的比较
- Hbase获取流程
- npm cnpm +nodejs
- 【经验总结】- IDEA无法显示Project目录怎么办