22,206
社区成员
发帖
与我相关
我的任务
分享
if not object_id(N'Tempdb..#tt') is null drop table #tt
GO
CREATE TABLE #tt(ItemID VARCHAR(10),BatchID VARCHAR(10),StockQty INT)
insert INTO #tt
SELECT 'A','111',2 UNION ALL
SELECT 'A','222',7 UNION ALL
SELECT 'A','333',6
DECLARE @OutQty INT =10
SELECT t1.ItemID,t1.BatchID,CASE WHEN ISNULL(p.p_qty,0)+t1.StockQty<=@OutQty THEN t1.StockQty ELSE @OutQty-ISNULL(p.p_qty,0) END AS UseQty FROM #tt AS t1
OUTER APPLY (SELECT SUM(tt.StockQty) AS p_qty FROM #tt AS tt WHERE tt.ItemID=t1.ItemID AND tt.BatchID<t1.BatchID) p
WHERE ISNULL(p.p_qty,0)<=@OutQty
ItemID BatchID UseQty
---------- ---------- -----------
A 111 2
A 222 7
A 333 1