计划订单如下所示:
订单编号 物料 数量
0001 A 100
0001 A 200
0001 A 300
0002 B 150
0002 B 100
现在传入数据
物料 数量(物料的数量肯定小于等于订单的物料总数量,例如A肯定为小于等于600)
A 400
B 150要求根据传入的数量将数量根据物料分到订单中,如果数量不足了,用零代替。
上述拆分的结果应该为:
0001 A 100
0001 A 200
0001 A 100
0002 B 150
0002 B 0不用循环和游标,如何实现。
订单编号 物料 数量
0001 A 100
0001 A 200
0001 A 300
0002 B 150
0002 B 100
现在传入数据
物料 数量(物料的数量肯定小于等于订单的物料总数量,例如A肯定为小于等于600)
A 400
B 150要求根据传入的数量将数量根据物料分到订单中,如果数量不足了,用零代替。
上述拆分的结果应该为:
0001 A 100
0001 A 200
0001 A 100
0002 B 150
0002 B 0不用循环和游标,如何实现。
感觉不是很妥
期待高手指点
create table #t1
(
pkid int identity (1,1),
number varchar(20),
type varchar(50),
num int
)
go
insert #t1 select '0001', 'A', 100
insert #t1 select '0001', 'A', 200
insert #t1 select '0001', 'A', 300
insert #t1 select '0002', 'B', 150
insert #t1 select '0002', 'B', 100
goselect number,type,
case when temp2-temp1 >= 0 then num
when temp2-temp1 < 0 and temp2 = temp3 then 0
else abs(num-temp2) end num from
(
select pkid,number,type,num,(select sum(num) from #t1 as bb where #t1.pkid>=bb.pkid and #t1.type=bb.type group by bb.type) temp1,
(select sum(num) from #t1 as bb where #t1.pkid>bb.pkid and #t1.type=bb.type group by bb.type) temp3,
case when type = 'A' then 400
when type = 'B' then 150 end temp2 from #t1
)tblselect * from #t1
case when temp2-temp1 >= 0 then num
when temp2-temp1 < 0 and temp2 = isnull(temp3,0) then 0
else abs(num-temp2) end num from
(
select pkid,number,type,num,(select sum(num) from #t1 as bb where #t1.pkid>=bb.pkid and #t1.type=bb.type group by bb.type) temp1,
(select sum(num) from #t1 as bb where #t1.pkid>bb.pkid and #t1.type=bb.type group by bb.type) temp3,
case when type = 'A' then 400
when type = 'B' then 150 end temp2 from #t1
)tbl
上述拆分的结果应该为:
0001 A 100
0001 A 200
0001 A 100
0002 B 150
0002 B 0 按你这个说法,第三行也是数量不足,为什么用100而不用0?
INSERT INTO #A SELECT '0001','A',100
INSERT INTO #A SELECT '0001','A',200
INSERT INTO #A SELECT '0001','A',300
INSERT INTO #A SELECT '0002','B',150
INSERT INTO #A SELECT '0002','B',100CREATE TABLE #B (S_NAME VARCHAR(20),QTY INT)INSERT INTO #B SELECT 'A',400
INSERT INTO #B SELECT 'B',150
SELECT IDENTITY(INT,1,1)SORT,A.*,(B.QTY)TOT_QTY INTO #C FROM #A A LEFT JOIN #B B ON A.S_NAME=B.S_NAME
SELECT S_NO,S_NAME,QTY, S_QTY=CASE WHEN TOT_QTY-(SELECT SUM(QTY) FROM #C WHERE S_NO=A.S_NO AND SORT<=A.SORT)>=QTY THEN QTY
WHEN TOT_QTY- (SELECT SUM(QTY) FROM #C WHERE S_NO=A.S_NO AND SORT<=A.SORT)>=0 THEN QTY
WHEN TOT_QTY- (SELECT SUM(QTY) FROM #C WHERE S_NO=A.S_NO AND SORT<=A.SORT)<0
AND TOT_QTY- (SELECT SUM(QTY) FROM #C WHERE S_NO=A.S_NO AND SORT<A.SORT)>0 THEN
TOT_QTY- (SELECT SUM(QTY) FROM #C WHERE S_NO=A.S_NO AND SORT<A.SORT) ELSE
0 END FROM #C ADROP TABLE #A,#B,#C/**0001 A 100 100
0001 A 200 200
0001 A 300 100
0002 B 150 150
0002 B 100 0
此问题也同样经常用到先进先出的运算中,与大家共勉。