#t 是库存表 #tt 是订单表 其中factqtyi 为实际分到的数量 orderqtyi 为订单数 productid 为产品表
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
