Sql -选择行，直到一行的和是某个值

I've seen other similar questions arround here, but they don't quite meet my needs, at least that's what I think.

我在这里也遇到过类似的问题，但它们并不完全符合我的需求，至少我是这么认为的。

I have a [reciepts] table with the following columns: reciept_id, customer_id, ammount ...

我有一个[reciepts]表，包含以下列:reciept_id、customer_id、ammount…

Lets Say:

让我们说:

I have 5 unpayed reciepts from customer 1:

我有5个客户的未付款收据1:

reciept_id: 1 | Ammount: 110€

reciept_id: 2 | Ammount: 110€

reciept_id: 3 | Ammount: 130€

reciept_id: 4 | Ammount: 110€

reciept_id: 5 | Ammount: 190€

So, customer 1, pays me 220€.

所以,客户支付我220€。

Now I need to select the oldest reciepts, until this 220€ sum is met, but only in a straight order, like (reciept 1 + reciept 2) and NOT like (reciept 1 + reciept 4).

现在我需要选择最古老reciepts,直到这220€总和,但只有在一个直接订单,(reciept 1 + reciept 2)和不喜欢(reciept 1 + reciept 4)。

Can you help me with the best query for this, or at least point me the best answer out there?

你能帮我解决这个问题吗?或者至少给我一个最好的答案?

Thanks in advance :)

提前谢谢:)

1 个解决方案

#1

Assuming that the sum will match the rows in sequence. Following query will work.

假设和将按顺序匹配行。下面的查询将工作。

DECLARE @Table TABLE(Reciept_Id INT , Amount INT)
INSERT INTO @Table
SELECT * 
FROM   (
         VALUES (1, 110),(2,110),(3,130),(4,110),(5,190)
       ) t (Reciept_Id, Amount) 

--Query

SELECT * FROM
(
    SELECT 
    Reciept_Id, 
    Amount,
    SUM(Amount) OVER(ORDER BY Reciept_Id ROWS 
       BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Total
FROM @Table
) T
WHERE T.Total <= 220

Output:

输出:

 Reciept_Id   Amount    Total
-----------   -------  ----------
    1          110      110
    2          110      220

Note : Query will work in SQL-Server 2012 and higher versions.

注意:查询将在SQL-Server 2012和更高版本中工作。

#1