您可以使用此谓词。

where Name = 'Foo' 
      and QtyRunningTotal - Quantity < @neededQty 

您需要找到总计中想要更多数量的地方。试试这个：

;WITH Products as 
(
Select 
    p.Name,sp.Price,sp.Quantity,SUM(sp.Quantity)OVER(PARTITION BY p.Name order by sp.Price,sp.Quantity desc) as QtyRunningTotal
from Product p
    inner join ProductDetails sp on sp.ProductId = p.ProductId
where productId IN (1,2,3,4)
)
select 
* from Products
where neededQty >= ABS(neededQty - QtyRunningTotal)

Name    Price   Quantity    QtyRunningTotal   Required  total-req 
------------------------------------------------------------------
Foo     1       15          15                10        -5        
Foo     1       15          30                10        -20        
Foo     1       15          45                10        -35        
Foo     1       15          60                10        -50        
Foo     1       100         160               10        -150        
Foo     1       100         260               10        -250        
Foo     1       100         360               10        -350        
Bar     1       10          10                10        0        
Bar     1       10          20                10        -10        
Bar     1       10          30                10        -20        

我计算出，根据您的数据集，您将需要一个范围。我还假设您想先拉回尽可能少的产品，即，如果我只需要15件商品，我会拉回记录说10和11，而不是拉回记录100。这是一个选择：>

CREATE TABLE #Product(ProductID int,ProdName varchar(20))
CREATE TABLE #ProductDetails(ProductID int,Price int,Quantity int)

INSERT INTO #Product(ProductID,ProdName) VALUES(1,'FOO')
INSERT INTO #Product(ProductID,ProdName) VALUES(2,'BAR')

INSERT INTO #ProductDetails(ProductID,Price,Quantity) VALUES(1,1,15)
INSERT INTO #ProductDetails(ProductID,16)
INSERT INTO #ProductDetails(ProductID,17)
INSERT INTO #ProductDetails(ProductID,18)
INSERT INTO #ProductDetails(ProductID,100)
INSERT INTO #ProductDetails(ProductID,101)
INSERT INTO #ProductDetails(ProductID,102)

INSERT INTO #ProductDetails(ProductID,Quantity) VALUES(2,10)
INSERT INTO #ProductDetails(ProductID,11)
INSERT INTO #ProductDetails(ProductID,12)

declare @neededQty int = 32

;WITH Products as 
(
Select 
    p.ProdName,LAG(sp.Quantity,0) OVER(PARTITION BY p.ProdName order by sp.Price,sp.Quantity  ) LG
from #Product p
    inner join #ProductDetails sp on sp.ProductId = p.ProductId
where p.productId IN (1,4)
),Prod2 AS
(
select *,SUM(LG) OVER(PARTITION BY ProdName ORDER BY Price,LG ) LowRange,SUM(Quantity) OVER(PARTITION BY ProdName ORDER BY Price,Quantity ) HighRange
from Products)

SELECT * 
FROM Prod2
WHERE LowRange < @neededQty OR  @neededQty >= HighRange