select itmref_0 as 产品编号,itmdes1_0 as 产品名称,stu_0 as 单位,
sum(decode(substr(vcrnum_0,0,6), 'PRCD11', qtystu_0)) 收货,
sum(decode(substr(vcrnum_0,0,6), 'PNHD11', qtystu_0)) 供应商退货,
sum(case when loc_0='D1101' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 购进,
sum(case when loc_0='D1101' and substr(trsfam_0,0,2)<>'IS' and qtystu_0>0 and trsfam_0<>'SDH' and substr(vcrnum_0,0,6)<>'PNHD11' then qtystu_0 end)本期调入,
sum(case when loc_0='D1101' and trsfam_0='SDH' then qtystu_0 end) 本期销售,
sum(case when loc_0='D1101' and trsfam_0= 'SRH' then qtystu_0 end)客户退货,
sum(case when loc_0='D1301' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end)江北购进,
sum(case when loc_0='D1301' and regflg_0='1' then qtystu_0 end) 江北库存,
sum(case when loc_0='D1301' and trsfam_0= 'SDH' then qtystu_0 end) 江北销售,
sum(case when loc_0='D1301' and trsfam_0= 'SRH' then qtystu_0 end) 江北客户退货,sum(case when loc_0='D1201' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 人民路购进,
sum(case when loc_0='D1201' and regflg_0='1' then qtystu_0 end ) 人民路库存,
sum(case when loc_0='D1201' and trsfam_0='SDH' then qtystu_0 end) 人民路销售,
sum(case when loc_0='D1201' and trsfam_0= 'SRH' then qtystu_0 end)人民路客户退货,
sum(case when loc_0='D1101' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 成品仓购进,
sum(case when loc_0='D1101' and regflg_0='1' then qtystu_0 end) 成品仓库存,
sum(case when loc_0='D1101' and trsfam_0= 'SDH' then qtystu_0 end) 成品仓销售,
sum(case when loc_0='D1101' and trsfam_0= 'SRH' then qtystu_0 end)成品仓客户退货,
sum(case when loc_0='D1501' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 办公仓购进,
sum(case when loc_0='D1501' and regflg_0='1' then qtystu_0 end) 办公仓库存,
sum(case when loc_0='D1501' and trsfam_0= 'SDH' then qtystu_0 end) 办公仓销售,
sum(case when loc_0='D1501' and trsfam_0= 'SRH' then qtystu_0 end)办公仓客户退货 ,
sum(decode(substr(trsfam_0,0,2), 'IS and loc_0="D1601"', qtystu_0)) 铺市其它出库,
sum(case when loc_0='D1601' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 铺市购进,
sum(decode(substr(trsfam_0,0,2), 'IS and loc_0="D1401"', qtystu_0)) 借用酒其它出库,
sum(case when loc_0='D1401' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 借用酒购进,
sum(decode(substr(trsfam_0,0,3), 'IS3 and loc_0="D1101"', qtystu_0)) 品尝其它出库,
sum(decode(substr(trsfam_0,0,3), 'IS2 and loc_0="D1101"', qtystu_0)) 报损其他出库,
sum(decode(substr(trsfam_0,0,2), 'EN', qtystu_0))其他入库 ,
sum(decode(substr(trsfam_0,0,2), 'IS', qtystu_0)) 其它出库
from(select
STOJOU.STOFCY_0,STOJOU.ITMREF_0,itmmaster.itmdes1_0,STOJOU.LOC_0,STOJOU.TRSFAM_0,STOJOU.stu_0,stojou.vcrnum_0,stojou.regflg_0,stojou.qtystu_0
from stojou left join itmmaster on stojou.itmref_0=itmmaster.itmref_0
where (STOJOU.stofcy_0='D11' OR STOJOU.STOFCY_0='D12')AND SUBSTR(STOJOU.ITMREF_0,0,1)='1' AND STOJOU.iptdat_0>to_date('2012-01-01','YYYY-MM-DD')-1
and STOJOU.iptdat_0<to_date('2012-01-31','YYYY-MM-DD')+1
group by
stojou.itmref_0,stojou.regflg_0,STOJOU.STOFCY_0,itmmaster.itmdes1_0,STOJOU.LOC_0,STOJOU.TRSFAM_0,STOJOU.stu_0,stojou.vcrnum_0,stojou.qtystu_0 order by stojou.itmref_0)
group by itmref_0,itmdes1_0,stu_0 order by itmref_0把下面的语句和上面的语句整合成一段select
sum(case when loc_0='D1301' and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 江北结存 ,
sum(case when loc_0='D1201' and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 人民路结存 ,
sum(case when loc_0='D1101' and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 成品仓结存 ,
sum(case when loc_0='D1501'and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 办公仓结存 ,
sum(case when stojou.stofcy_0='D11'AND stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 期末
from stojou
sum(decode(substr(vcrnum_0,0,6), 'PRCD11', qtystu_0)) 收货,
sum(decode(substr(vcrnum_0,0,6), 'PNHD11', qtystu_0)) 供应商退货,
sum(case when loc_0='D1101' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 购进,
sum(case when loc_0='D1101' and substr(trsfam_0,0,2)<>'IS' and qtystu_0>0 and trsfam_0<>'SDH' and substr(vcrnum_0,0,6)<>'PNHD11' then qtystu_0 end)本期调入,
sum(case when loc_0='D1101' and trsfam_0='SDH' then qtystu_0 end) 本期销售,
sum(case when loc_0='D1101' and trsfam_0= 'SRH' then qtystu_0 end)客户退货,
sum(case when loc_0='D1301' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end)江北购进,
sum(case when loc_0='D1301' and regflg_0='1' then qtystu_0 end) 江北库存,
sum(case when loc_0='D1301' and trsfam_0= 'SDH' then qtystu_0 end) 江北销售,
sum(case when loc_0='D1301' and trsfam_0= 'SRH' then qtystu_0 end) 江北客户退货,sum(case when loc_0='D1201' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 人民路购进,
sum(case when loc_0='D1201' and regflg_0='1' then qtystu_0 end ) 人民路库存,
sum(case when loc_0='D1201' and trsfam_0='SDH' then qtystu_0 end) 人民路销售,
sum(case when loc_0='D1201' and trsfam_0= 'SRH' then qtystu_0 end)人民路客户退货,
sum(case when loc_0='D1101' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 成品仓购进,
sum(case when loc_0='D1101' and regflg_0='1' then qtystu_0 end) 成品仓库存,
sum(case when loc_0='D1101' and trsfam_0= 'SDH' then qtystu_0 end) 成品仓销售,
sum(case when loc_0='D1101' and trsfam_0= 'SRH' then qtystu_0 end)成品仓客户退货,
sum(case when loc_0='D1501' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 办公仓购进,
sum(case when loc_0='D1501' and regflg_0='1' then qtystu_0 end) 办公仓库存,
sum(case when loc_0='D1501' and trsfam_0= 'SDH' then qtystu_0 end) 办公仓销售,
sum(case when loc_0='D1501' and trsfam_0= 'SRH' then qtystu_0 end)办公仓客户退货 ,
sum(decode(substr(trsfam_0,0,2), 'IS and loc_0="D1601"', qtystu_0)) 铺市其它出库,
sum(case when loc_0='D1601' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 铺市购进,
sum(decode(substr(trsfam_0,0,2), 'IS and loc_0="D1401"', qtystu_0)) 借用酒其它出库,
sum(case when loc_0='D1401' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 借用酒购进,
sum(decode(substr(trsfam_0,0,3), 'IS3 and loc_0="D1101"', qtystu_0)) 品尝其它出库,
sum(decode(substr(trsfam_0,0,3), 'IS2 and loc_0="D1101"', qtystu_0)) 报损其他出库,
sum(decode(substr(trsfam_0,0,2), 'EN', qtystu_0))其他入库 ,
sum(decode(substr(trsfam_0,0,2), 'IS', qtystu_0)) 其它出库
from(select
STOJOU.STOFCY_0,STOJOU.ITMREF_0,itmmaster.itmdes1_0,STOJOU.LOC_0,STOJOU.TRSFAM_0,STOJOU.stu_0,stojou.vcrnum_0,stojou.regflg_0,stojou.qtystu_0
from stojou left join itmmaster on stojou.itmref_0=itmmaster.itmref_0
where (STOJOU.stofcy_0='D11' OR STOJOU.STOFCY_0='D12')AND SUBSTR(STOJOU.ITMREF_0,0,1)='1' AND STOJOU.iptdat_0>to_date('2012-01-01','YYYY-MM-DD')-1
and STOJOU.iptdat_0<to_date('2012-01-31','YYYY-MM-DD')+1
group by
stojou.itmref_0,stojou.regflg_0,STOJOU.STOFCY_0,itmmaster.itmdes1_0,STOJOU.LOC_0,STOJOU.TRSFAM_0,STOJOU.stu_0,stojou.vcrnum_0,stojou.qtystu_0 order by stojou.itmref_0)
group by itmref_0,itmdes1_0,stu_0 order by itmref_0把下面的语句和上面的语句整合成一段select
sum(case when loc_0='D1301' and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 江北结存 ,
sum(case when loc_0='D1201' and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 人民路结存 ,
sum(case when loc_0='D1101' and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 成品仓结存 ,
sum(case when loc_0='D1501'and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 办公仓结存 ,
sum(case when stojou.stofcy_0='D11'AND stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 期末
from stojou
解决方案 »
- oracle 对象调用成员函数问题
- sqlplus输出问题
- 有cognos商业智能的中文教程或视频吗?
- 关于查询的问题
- Oracel有类似sql server profiler的SQL语句监控器吗?
- 怎样在C++程序中判断oracle数据库的可用性还有指定数据文件的读写次数?
- 如何获取一个表空间下已有的所有表名?(在线等待)
- 配置好Configuration Assistant后,启动OracleOraHome81ManagementServer服务出现奇怪错误!
- Oracle中如何多条件全文检索???????????(急!!!!!!!!)
- 欢迎赐教,在线,急!!!
- 关于oracle
- 如何转换成Oracle代码?高分求!
select 产品编号,产品名称,单位,sum(收货) 收货....sum(所有数字字段)...
from (
select itmref_0 as 产品编号,itmdes1_0 as 产品名称,stu_0 as 单位,
sum(decode(substr(vcrnum_0,0,6), 'PRCD11', qtystu_0)) 收货,
sum(decode(substr(vcrnum_0,0,6), 'PNHD11', qtystu_0)) 供应商退货,
sum(case when loc_0='D1101' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 购进,
sum(case when loc_0='D1101' and substr(trsfam_0,0,2)<>'IS' and qtystu_0>0 and trsfam_0<>'SDH' and substr(vcrnum_0,0,6)<>'PNHD11' then qtystu_0 end)本期调入,
sum(case when loc_0='D1101' and trsfam_0='SDH' then qtystu_0 end) 本期销售,
sum(case when loc_0='D1101' and trsfam_0= 'SRH' then qtystu_0 end)客户退货,
sum(case when loc_0='D1301' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end)江北购进,
sum(case when loc_0='D1301' and regflg_0='1' then qtystu_0 end) 江北库存,
sum(case when loc_0='D1301' and trsfam_0= 'SDH' then qtystu_0 end) 江北销售,
sum(case when loc_0='D1301' and trsfam_0= 'SRH' then qtystu_0 end) 江北客户退货,sum(case when loc_0='D1201' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 人民路购进,
sum(case when loc_0='D1201' and regflg_0='1' then qtystu_0 end ) 人民路库存,
sum(case when loc_0='D1201' and trsfam_0='SDH' then qtystu_0 end) 人民路销售,
sum(case when loc_0='D1201' and trsfam_0= 'SRH' then qtystu_0 end)人民路客户退货,
sum(case when loc_0='D1101' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 成品仓购进,
sum(case when loc_0='D1101' and regflg_0='1' then qtystu_0 end) 成品仓库存,
sum(case when loc_0='D1101' and trsfam_0= 'SDH' then qtystu_0 end) 成品仓销售,
sum(case when loc_0='D1101' and trsfam_0= 'SRH' then qtystu_0 end)成品仓客户退货,
sum(case when loc_0='D1501' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 办公仓购进,
sum(case when loc_0='D1501' and regflg_0='1' then qtystu_0 end) 办公仓库存,
sum(case when loc_0='D1501' and trsfam_0= 'SDH' then qtystu_0 end) 办公仓销售,
sum(case when loc_0='D1501' and trsfam_0= 'SRH' then qtystu_0 end)办公仓客户退货 ,
sum(decode(substr(trsfam_0,0,2), 'IS and loc_0="D1601"', qtystu_0)) 铺市其它出库,
sum(case when loc_0='D1601' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 铺市购进,
sum(decode(substr(trsfam_0,0,2), 'IS and loc_0="D1401"', qtystu_0)) 借用酒其它出库,
sum(case when loc_0='D1401' and substr(trsfam_0,0,2)<>'EN' and trsfam_0<>'SRH' and qtystu_0>0 then qtystu_0 end) 借用酒购进,
sum(decode(substr(trsfam_0,0,3), 'IS3 and loc_0="D1101"', qtystu_0)) 品尝其它出库,
sum(decode(substr(trsfam_0,0,3), 'IS2 and loc_0="D1101"', qtystu_0)) 报损其他出库,
sum(decode(substr(trsfam_0,0,2), 'EN', qtystu_0))其他入库 ,
sum(decode(substr(trsfam_0,0,2), 'IS', qtystu_0)) 其它出库,
0 江北结存,0 人民路结存,0 成品仓结存, 0 办公仓结存,0 期末
from(select
STOJOU.STOFCY_0,STOJOU.ITMREF_0,itmmaster.itmdes1_0,STOJOU.LOC_0,STOJOU.TRSFAM_0,STOJOU.stu_0,stojou.vcrnum_0,stojou.regflg_0,stojou.qtystu_0
from stojou left join itmmaster on stojou.itmref_0=itmmaster.itmref_0
where (STOJOU.stofcy_0='D11' OR STOJOU.STOFCY_0='D12')AND SUBSTR(STOJOU.ITMREF_0,0,1)='1' AND STOJOU.iptdat_0>to_date('2012-01-01','YYYY-MM-DD')-1
and STOJOU.iptdat_0<to_date('2012-01-31','YYYY-MM-DD')+1
group by
stojou.itmref_0,stojou.regflg_0,STOJOU.STOFCY_0,itmmaster.itmdes1_0,STOJOU.LOC_0,STOJOU.TRSFAM_0,STOJOU.stu_0,stojou.vcrnum_0,stojou.qtystu_0 order by stojou.itmref_0) as a
group by itmref_0,itmdes1_0,stu_0 order by itmref_0union allselect itmref_0,itmdes1_0,stu_0,0,0,0,0,0,0,0,...(第一段代码中有多少个计算字段就有多少0),
sum(case when loc_0='D1301' and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 江北结存 ,
sum(case when loc_0='D1201' and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 人民路结存 ,
sum(case when loc_0='D1101' and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 成品仓结存 ,
sum(case when loc_0='D1501'and regflg_0='1'and stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 办公仓结存 ,
sum(case when stojou.stofcy_0='D11'AND stojou.iptdat_0<=to_date('2012-02-28','yyyy-mm-dd')
then stojou.qtystu_0 else 0 end) as 期末
from stojou
) b
group by 产品编号,产品名称,单位