select Row_Number() over(order by 母件编码,子件编码) as id ,szdc_BOM.*,ap.apqty as '请购数',po.poqty as '采购在途',om.omqty as '委外在途',mo.moqty as '生产在制',omm.ommqty as '委外未领数',mom.momqty as '生产未领数' into #a from szdc_BOM left join (select cInvCode,sum(isnull(fQuantity,0)-isnull(iReceivedQTY,0)) as apqty from PU_AppVouch a inner join PU_AppVouchs b on a.ID=b.ID where isnull(iReceivedQTY,0)'4' and ISNULL(QualifiedInQty,0)

select a.*, (select sum(isnull(订单用量,0)) from #A b where b.子件编码=a.子件编码 and b.id<=a.id) 'qty' into #b from #a a select (case when isnull(isnull(c.iquantity,0)-isnull(b.qty,0)-isnull(委外未领数,0)-isnull(生产未领数,0),0)>0 then 0 else isnull(isnull(c.iquantity,0)-isnull(b.qty,0)-isnull(委外未领数,0)-isnull(生产未领数,0),0) end) as '短缺数量',c.iquantity as '现存量',b.* into #c from #b b left join (select cInvCode,SUM(ISNULL(iquantity,0)) as iquantity from currentstock group by cinvcode ) c on b.子件编码=c.cinvcode select 子件编码,子件名称,子件规格,单位,Min(短缺数量) as '短缺数量',请购数,采购在途,委外在途,生产在制,委外未领数,生产未领数,现存量 from #c where 短缺数量<0 group by 子件编码,子件名称,子件规格,单位,请购数,采购在途,委外在途,生产在制,委外未领数,生产未领数,现存量