22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #t(productid INT,qtyi INT)
INSERT #t
( productid, qtyi )
SELECT 1,50 UNION ALL
SELECT 2,23 UNION ALL
SELECT 3,76 UNION ALL
SELECT 4,85 UNION ALL
SELECT 5,40 UNION ALL
SELECT 6,90
CREATE TABLE #tt (branchid VARCHAR(20), productid INT,orderqtyi INT,factqtyi INT)
INSERT #tt
( branchid , productid , orderqtyi , factqtyi )
SELECT 'A1',1,8,0 UNION ALL
SELECT 'A2',1,18,0 UNION ALL
SELECT 'A3',1,29,0 UNION ALL
SELECT 'B1',2,7,0 UNION ALL
SELECT 'B2',2,8,0 UNION ALL
SELECT 'B3',2,9,0 UNION ALL
SELECT 'B4',2,18,0 UNION ALL
SELECT 'C1',3,11,0 UNION ALL
SELECT 'C2',3,23,0 UNION ALL
SELECT 'C3',3,43,0 UNION ALL
SELECT 'D1',4,52,0 UNION ALL
SELECT 'D2',4,19,0 UNION ALL
SELECT 'E1',5,14,0 UNION ALL
SELECT 'E2',5,17,0 UNION ALL
SELECT 'E3',5,65,0
SELECT a.qtyi,B.* INTO #t3 FROM #t a INNER JOIN #tt b ON a.productid = b.productid
DECLARE @orderqtyi INT ,@qtyi INT ,@tran INT ,@branchid VARCHAR(5),@productid int
SET @orderqtyi=0
SET @qtyi=100
SET @tran=0
SET @branchid=''
SET @productid=0
UPDATE #t3
SET @tran=CASE WHEN @productid=productid THEN 0 ELSE 1 END ,
@productid=productid,
@qtyi=CASE WHEN @tran=1 THEN qtyi ELSE
CASE WHEN @qtyi>@orderqtyi THEN @qtyi-@orderqtyi ELSE 0 END
END ,
@orderqtyi=orderqtyi,
factqtyi=CASE WHEN @qtyi>0 THEN
CASE WHEN orderqtyi>@qtyi THEN @qtyi ELSE orderqtyi END
ELSE 0 END
SELECT * FROM #t3
DROP TABLE #t
DROP TABLE #tt
DROP TABLE #t3