ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查询采购入库状况

查询采购入库状况

原创 Linux操作系统 作者:zaorv 时间:2019-04-28 21:06:05 0 删除 编辑

---查询采购入库状况.
select purstk.* , isnull(TH.thsumqty,0) as thSumQty,purstk.deffQTY-isnull(TH.THSUMQTY,0) as variance from
(
select pur.purNo,rk.RkNo,pur.matno,mat.mat_Nm,pur.citem,pur.cgUnit,pur.cgSumqty,rk.rkSumQTY
, (RK.RKsumQTY-PUR.CGSUMQTY) AS deffQTY from
(Select pno as purNo,cItem,matno,cgUnit,sum(cg_QTY) as cgSumQty from mat_cg_c group by pno,citem,matno,cgUnit ) pur,
(select a.*,mat_ysmx_c.ysno as rkNo from
(select pno_tmp as purNo,citem,matno,sum(ys_qty) as RkSumQty
from mat_ysmx_c group by pno_tmp,citem,matno) a
,mat_ysmx_c where a.purno=mat_ysmx_c.pno_tmp and a.citem=mat_ysmx_c.citem ) RK
,(select matno,mat_nm from Mat_Material) mat
where pur.purNo=RK.purNo and pur.citem=rk.citem and mat.matNo=pur.matno
) purstk
left join (select pno_tmp as thNo,pno_tmp as RkNo,citem,matno,sum(ys_qty) as ThSumQty from mat_thmx_c
group by pno_tmp,citem,matno,pno_tmp) as th
on purstk.citem=th.citem and purstk.rkno=th.rkno
order by purstk.purNo

多谢小刘指导用Left join 。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/729024/viewspace-564851/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-09-12

  • 博文量
    192
  • 访问量
    153437