DDL - SQL查询、修改

-- 查询 垫布的颜色/尺码
-- 工作单物料需求-物料需求明细

select m.OrderNo,m.MatrClass,m.MatrCode,m.Color,m.Sizx 
from mrjmdtl m 
where m.MatrClass = 'DB' 
into dsResult1;

-- 查询出料单中已出库且无单价的物料信息
-- 出料单（每一笔出库数据）/物料名称/收货方/出库金额=0
-- 订单编号/款式/物料种类/物料编码/物料名称/出料单编号/颜色/尺码/出料数量/单位/出库时间/金额/出库的供应商

select i.orderNo,i.Style,i.MatrClass,i.MatrCode,matr.ShortName, 
		i.IssDocRef,i.Color,i.Sizx,i.AlcQty,i.QtyUnit,i.IssDate,i.Amt,s.name
from ivwpmatr i
left join ivissdoc doc on i.IssDocRef = doc.IssDocRef
left join sycmftr s on doc.SendTo = s.Code
left join sygmatr matr on i.MatrCode = matr.MatrCode
where i.issdate between "2023/05/29" and "2023/05/29"
and i.amt = 0
into dsResult1;

-- 根据无单价出料单，查询出料单中的批号信息
-- 出料单编号/批号

select ds.IssDocRef,ivs.LotNo
from dsResult1 ds
left join ivismatr iv on ds.IssDocRef = iv.IssDocRef and ds.MatrCode = iv.MatrCode
left join ivskcrd ivs on iv.LotID = ivs.LotID 
group by ds.IssDocRef,iv.LotID,ivs.LotNo
into dsResult2;

 

-- 仓储系统，清空库存数据

-- UPDATE stock set xs = 0,sl = 0,updateTime = NOW() where xsddMxId in
 select * from stock where xsddMxId in 
 (select id from xsdd_mx where xsddId in 
 (select id from xsdd where salesMan = 'name'))
-- UPDATE stock SET updateTime = NOW() where id = 4851