t-sql 循环遍历所有行并从列中求和，直到达到值 [英] t-sql loop through all rows and sum amount from column until value is reached

问题描述

我有一个包含以下测试数据的表格:

I have a table containing the below test data:

我现在想让一家餐厅有 12 个座位.

I now would like to fill a restaurant with 12 seating spaces.

这应该导致:

基本上，我需要从上到下遍历所有行并添加 AmountPersons，直到填满餐厅.

Basically, I need to loop from top to bottom through all rows and add the AmountPersons until I have filled the restaurant.

在这个例子中:

(前几行:AmountPersons)3+1+2+4 = 10

(first few rows: AmountPersons) 3+1+2+4 = 10

UserId 52 无法添加，因为他们为 3 人预留了位置，这将导致 13 个位置被占用，只有 12 个可用.

UserId 52 can't be added because they reserved for 3 persons, which would result in 13 occupied places and there are only 12 available.

在下一行，它注意到有 1 个预订.这可以添加到我们已经找到的前 10 个中.

In the next row it notices a reservation for 1. This can be added to the previous 10 we already found.

NewTotal 现在是 11.

NewTotal is now 11.

无法添加 UserId 79 和 82，因为我们会再次超出容量.

UserId 79 and 82 can't be added because we'd exceed the capacity again.

UserId 95 保留为 1，可以添加这个，现在我们已经填满了所有位置.

UserId 95 reserved for 1, this one can be added and we now have all places filled.

这是我从我使用的游标中得到的结果，但我现在卡住了.请帮忙.

This is the result I get from the cursor I use, but I'm stuck now. Please help.

当下一个值大于 12 时，光标中的 while 循环基本上停止.但这是不正确的.

The while loop I have in the cursor basically stops when the next value would be higher than 12. But that is not correct.

推荐答案

因为要跳过行，所以需要递归 CTE.但这很棘手——因为您可能没有遵循您的规则的小组加起来正好是 12.

Because you want to skip rows, you need a recursive CTE. But it is tricky -- because you may not have a group following your rules that adds up to exactly 12.

所以:

with tn as (
         select t.*, row_number() over (order by userid) as seqnum
         from t
        ),
       cte as (
        select userId, name, amountPersons as total, 1 as is_included, seqnum
        from tn 
        where seqnum = 1
        union all
        select tn.userId, tn.name, 
               (case when tn.amountPersons + cte.total <= 12
                     then tn.amountPersons + cte.total
                     else cte.total
                end),
               (case when tn.amountPersons + cte.total <= 12
                     then 1
                     else 0
                end) as is_included,
               tn.seqnum
        from cte join
             tn
             on tn.seqnum = cte.seqnum + 1
        where cte.total < 12
    )
select cte.*
from cte
where is_included = 1;

这里是db<>fiddle.

Here is a db<>fiddle.

请注意，如果将I"更改为更大的值，则不包括在内，占用的座位数为 11，而不是 12.

Note that if you change "I" to a larger value, then it is not included and the number of occupied seats is 11, not 12.

这篇关于t-sql 循环遍历所有行并从列中求和，直到达到值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！