27,579
社区成员
发帖
与我相关
我的任务
分享
select
半成品=t2.wlcode,
半成品数量=MIN(t1.wlnumber)
from
wlinWM t1
inner join relation t2 on substring(t1.wlcode,0,8)=t2.wlcode
group by t2.wlcode
having COUNT(t1.wlcode)=COUNT(t2.wlcode1)--零件的数量相同
with cte1 --半成品数量
as
(
select
半成品=t2.wlcode,
半成品数量=MIN(t1.wlnumber)
from
wlinWM t1
inner join wlinfo t2 on substring(t1.wlcode,0,8)=t2.wlcode
group by t2.wlcode
),
cte2 -- 剩余材料 数量
as
(
select
剩余材料=t1.wlcode
,剩余材料数量=t1.wlnumber-t2.半成品数量
from
wlinWM t1
inner join cte1 t2 on substring(t1.wlcode,0,8)=t2.半成品
where
t1.wlnumber>t2.半成品数量
),
cte3 --成品 数量
as
(
select
成品=substring(t1.半成品,0,4),
成品数量=MIN(t1.半成品数量)
from
cte1 t1
group by substring(t1.半成品,0,4)
)
select * from cte1 --半成品
union all
select * from cte2 --剩余材料
union all
select * from cte3 --成品
/*
半成品数量 剩余材料数量
----------- -----------
4 6
成品数量 半成品数量
----------- -----------
2 6
*/