把y销售订单量、y库存量、y在途入库量做为字查询即可 即select * from y销售订单量 x,y库存量 y,y在途入库量 z where x.存货管理id = y.存货管理id and x.存货管理id = z.存货管理id
select m1.*,m2.*,m3.* from( select 'col00001' as col1, sum(a.sales_num)/15*5, a.goods_id,a.goods_name,c.pk_invbasdoc 存货管理id from t_order_goods a,bd_goods b ,bd_invbasdoc c where a.ts between to_char (trunc(sysdate-15),'YYYY-MM-DD HH:mm:ss') and to_char(trunc(sysdate+1),'YYYY-MM-DD HH:mm:ss') and a.goods_id=b.goods_id and b.pk_invbasdoc=c.pk_invbasdoc and a.dr=0 and c.dr=0 group by a.goods_id,a.goods_name,c.pk_invbasdoc) m1 left join( select 'col00001' as col1,sum(nonhandnum),cinventoryid 存货管理id from ic_onhandnum where dr=0 group by cinventoryid ) m2 on m1.col1 = m2.col2 left join( select 'col00001' as col1,a.corder_bid,a.corderid,c.nshouldinnum 应入数量,c.cinventoryid 存货管理id from po_order_b a,---采购订单体 po_order b,--采购订单头id ic_general_b c ---出入库单表体 where a.corderid=b.corderid and b.corderid=c.cfirstbillhid and a.iisactive = 0 ---是否激活 and b.breturn='N' --是否退货 and c.bonroadflag<>'N' ---bonroadflag是否在途 and a.dr=0 and b.dr=0 and c.dr=0 ) m3 on m1.col1 = m3.col1
select m1.value1-(m2.value2+m3.value3) from( select 'col00001' as col1, sum(a.sales_num)/15*5 value1, a.goods_id,a.goods_name,c.pk_invbasdoc 存货管理id from t_order_goods a,bd_goods b ,bd_invbasdoc c where a.ts between to_char (trunc(sysdate-15),'YYYY-MM-DD HH:mm:ss') and to_char(trunc(sysdate+1),'YYYY-MM-DD HH:mm:ss') and a.goods_id=b.goods_id and b.pk_invbasdoc=c.pk_invbasdoc and a.dr=0 and c.dr=0 group by a.goods_id,a.goods_name,c.pk_invbasdoc) m1 left join( select 'col00001' as col1,sum(nonhandnum) value2,cinventoryid 存货管理id from ic_onhandnum where dr=0 group by cinventoryid ) m2 on m1.col1 = m2.col2 left join( select 'col00001' as col1,a.corder_bid,a.corderid,c.nshouldinnum value3,c.cinventoryid 存货管理id from po_order_b a,---采购订单体 po_order b,--采购订单头id ic_general_b c ---出入库单表体 where a.corderid=b.corderid and b.corderid=c.cfirstbillhid and a.iisactive = 0 ---是否激活 and b.breturn='N' --是否退货 and c.bonroadflag<>'N' ---bonroadflag是否在途 and a.dr=0 and b.dr=0 and c.dr=0 ) m3 on m1.col1 = m3.col1
select aa.value1-(bb.value2+cc.value3) from( Select sum(a.sales_num)/15*5 value1, a.goods_id,a.goods_name,c.pk_invbasdoc 存货管理id from t_order_goods a,bd_goods b ,bd_invbasdoc c where a.ts between to_char (trunc(sysdate-15),'YYYY-MM-DD HH:mm:ss') and to_char(trunc(sysdate+1),'YYYY-MM-DD HH:mm:ss') and a.goods_id=b.goods_id and b.pk_invbasdoc=c.pk_invbasdoc and a.dr=0 and c.dr=0 group by a.goods_id,a.goods_name,c.pk_invbasdoc) aa left join( Select sum(nonhandnum) value2,cinventoryid 存货管理id from ic_onhandnum where dr=0 group by cinventoryid ) bb on aa.pk_invbasdoc = bb.cinventoryid left join( Select a.corder_bid,a.corderid,c.nshouldinnum value3,c.cinventoryid cinventoryid from po_order_b a,---采购订单体 po_order b,--采购订单头id ic_general_b c ---出入库单表体 where a.corderid=b.corderid and b.corderid=c.cfirstbillhid and a.iisactive = 0 ---是否激活 and b.breturn='N' --是否退货 and c.bonroadflag<>'N' ---bonroadflag是否在途 and a.dr=0 and b.dr=0 and c.dr=0 ) cc on aa.aapk_invbasdoc = cc.cinventoryid
select nvl(aa.value1,0)-nvl(bb.value2,0)+nvl(cc.value3,0),aa.goods_name from( select sum(a.sales_num)/15*5 value1, a.goods_id,a.goods_name,c.pk_invbasdoc from t_order_goods a,bd_goods b ,bd_invbasdoc c,t_order_info d where d.create_time between trunc(sysdate-15) and trunc(sysdate+1)--to_char(trunc(sysdate+1),'YYYY-MM-DD HH:mm:ss') and a.pk_order_no=d.pk_order_no and a.goods_id=b.goods_id and b.pk_invbasdoc=c.pk_invbasdoc and a.dr=0 and c.dr=0 group by a.goods_id,a.goods_name,c.pk_invbasdoc) aa left join( Select sum(nonhandnum) value2,cinvbasid from ic_onhandnum where dr=0 group by cinvbasid ) bb on aa.pk_invbasdoc = bb.cinvbasid left join( Select a.corder_bid,a.corderid,c.nshouldinnum value3,c.cinvbasid cinvbasid from po_order_b a,---采购订单体 po_order b,--采购订单头id ic_general_b c ---出入库单表体 where a.corderid=b.corderid and b.corderid=c.cfirstbillhid and a.iisactive = 0 ---是否激活 and b.breturn='N' --是否退货 and c.bonroadflag<>'N' ---bonroadflag是否在途 and a.dr=0 and b.dr=0 and c.dr=0 ) cc on aa.pk_invbasdoc = cc.cinvbasid 根据上面各楼的答案我终于弄出来了根据---y销售订单量 表的信息,就是不符合我的要求,根据 存货档案表bd_invbasdoc表 上面我写错了对应的字段在上面代码
补充一下
这里的公式必须围绕 bd_invbasdoc表中的pk_invbasdoc 存货管理id
的所有表记录,就是依照bd_invbasdoc写
即select * from y销售订单量 x,y库存量 y,y在途入库量 z where x.存货管理id = y.存货管理id and x.存货管理id = z.存货管理id
select m1.*,m2.*,m3.* from(
select 'col00001' as col1, sum(a.sales_num)/15*5, a.goods_id,a.goods_name,c.pk_invbasdoc 存货管理id from t_order_goods a,bd_goods b ,bd_invbasdoc c
where a.ts between to_char (trunc(sysdate-15),'YYYY-MM-DD HH:mm:ss') and to_char(trunc(sysdate+1),'YYYY-MM-DD HH:mm:ss')
and a.goods_id=b.goods_id
and b.pk_invbasdoc=c.pk_invbasdoc
and a.dr=0
and c.dr=0
group by a.goods_id,a.goods_name,c.pk_invbasdoc) m1
left join(
select 'col00001' as col1,sum(nonhandnum),cinventoryid 存货管理id from ic_onhandnum
where dr=0
group by cinventoryid
) m2 on m1.col1 = m2.col2
left join(
select 'col00001' as col1,a.corder_bid,a.corderid,c.nshouldinnum 应入数量,c.cinventoryid 存货管理id from po_order_b a,---采购订单体
po_order b,--采购订单头id
ic_general_b c ---出入库单表体
where a.corderid=b.corderid
and b.corderid=c.cfirstbillhid
and a.iisactive = 0 ---是否激活
and b.breturn='N' --是否退货
and c.bonroadflag<>'N' ---bonroadflag是否在途
and a.dr=0
and b.dr=0
and c.dr=0
) m3 on m1.col1 = m3.col1
select 'col00001' as col1, sum(a.sales_num)/15*5 value1, a.goods_id,a.goods_name,c.pk_invbasdoc 存货管理id from t_order_goods a,bd_goods b ,bd_invbasdoc c
where a.ts between to_char (trunc(sysdate-15),'YYYY-MM-DD HH:mm:ss') and to_char(trunc(sysdate+1),'YYYY-MM-DD HH:mm:ss')
and a.goods_id=b.goods_id
and b.pk_invbasdoc=c.pk_invbasdoc
and a.dr=0
and c.dr=0
group by a.goods_id,a.goods_name,c.pk_invbasdoc) m1
left join(
select 'col00001' as col1,sum(nonhandnum) value2,cinventoryid 存货管理id from ic_onhandnum
where dr=0
group by cinventoryid
) m2 on m1.col1 = m2.col2
left join(
select 'col00001' as col1,a.corder_bid,a.corderid,c.nshouldinnum value3,c.cinventoryid 存货管理id from po_order_b a,---采购订单体
po_order b,--采购订单头id
ic_general_b c ---出入库单表体
where a.corderid=b.corderid
and b.corderid=c.cfirstbillhid
and a.iisactive = 0 ---是否激活
and b.breturn='N' --是否退货
and c.bonroadflag<>'N' ---bonroadflag是否在途
and a.dr=0
and b.dr=0
and c.dr=0
) m3 on m1.col1 = m3.col1
Select sum(a.sales_num)/15*5 value1, a.goods_id,a.goods_name,c.pk_invbasdoc 存货管理id from t_order_goods a,bd_goods b ,bd_invbasdoc c
where a.ts between to_char (trunc(sysdate-15),'YYYY-MM-DD HH:mm:ss') and to_char(trunc(sysdate+1),'YYYY-MM-DD HH:mm:ss')
and a.goods_id=b.goods_id
and b.pk_invbasdoc=c.pk_invbasdoc
and a.dr=0
and c.dr=0
group by a.goods_id,a.goods_name,c.pk_invbasdoc) aa
left join(
Select sum(nonhandnum) value2,cinventoryid 存货管理id from ic_onhandnum
where dr=0
group by cinventoryid
) bb on aa.pk_invbasdoc = bb.cinventoryid
left join(
Select a.corder_bid,a.corderid,c.nshouldinnum value3,c.cinventoryid cinventoryid from po_order_b a,---采购订单体
po_order b,--采购订单头id
ic_general_b c ---出入库单表体
where a.corderid=b.corderid
and b.corderid=c.cfirstbillhid
and a.iisactive = 0 ---是否激活
and b.breturn='N' --是否退货
and c.bonroadflag<>'N' ---bonroadflag是否在途
and a.dr=0
and b.dr=0
and c.dr=0
) cc on aa.aapk_invbasdoc = cc.cinventoryid
select sum(a.sales_num)/15*5 value1, a.goods_id,a.goods_name,c.pk_invbasdoc from t_order_goods a,bd_goods b ,bd_invbasdoc c,t_order_info d
where d.create_time between trunc(sysdate-15) and trunc(sysdate+1)--to_char(trunc(sysdate+1),'YYYY-MM-DD HH:mm:ss')
and a.pk_order_no=d.pk_order_no
and a.goods_id=b.goods_id
and b.pk_invbasdoc=c.pk_invbasdoc
and a.dr=0
and c.dr=0
group by a.goods_id,a.goods_name,c.pk_invbasdoc) aa
left join(
Select sum(nonhandnum) value2,cinvbasid from ic_onhandnum
where dr=0
group by cinvbasid
) bb on aa.pk_invbasdoc = bb.cinvbasid
left join(
Select a.corder_bid,a.corderid,c.nshouldinnum value3,c.cinvbasid cinvbasid from po_order_b a,---采购订单体
po_order b,--采购订单头id
ic_general_b c ---出入库单表体
where a.corderid=b.corderid
and b.corderid=c.cfirstbillhid
and a.iisactive = 0 ---是否激活
and b.breturn='N' --是否退货
and c.bonroadflag<>'N' ---bonroadflag是否在途
and a.dr=0
and b.dr=0
and c.dr=0
) cc on aa.pk_invbasdoc = cc.cinvbasid
根据上面各楼的答案我终于弄出来了根据---y销售订单量 表的信息,就是不符合我的要求,根据 存货档案表bd_invbasdoc表 上面我写错了对应的字段在上面代码