如何只得到行中每一行添加一个字段组成一个必需的量?

I have a quantity value which can be anything. For this example lets assume it is 75.

我有一个可以是任何值的量。对于这个例子，假设它是75。

I have a query which returns a set of rows showing the number of items in different bins scattered around the place (physically). The list is in a sorted order.

我有一个查询，它返回一组行，显示了分散在各处(物理上)的不同容器中的项的数量。这个列表是按顺序排列的。

I want to filter the rows to show only the rows that are needed to fulfil the required quantity. These rows are in a sorted order and will always be required in a sorted order, i.e. only row 1 or rows 1 and 2, or 1,2,3 etc. but never rows 1,3,5 for example.

我想过滤这些行，只显示满足所需数量所需的行。这些行是按排序的顺序排列的，并且总是按排序的顺序被要求，例如，只有第1行或第1行和第2行，或第1、2、3行等等，但是从来没有第1、3、5行。

Here is an example that needs filtering to return only the first 2 rows for quantity = 75:

下面是一个需要过滤的示例，仅返回数量= 75的前两行:

row  item count
1       20
2       60
3        7
4       20

If some sort of running total or quantity still required and a count of items remaining is possible that would be great too, but that might be a step too far.

如果仍然需要某种运行的总量或数量，并且还可能有项的计数，那就太好了，但这可能有点过头了。

2 个解决方案

#1

SQL Server 2012 has direct support for cumulative sums. In earlier versions, you need to do the calculation differently. I like doing it with a correlated subquery:

SQL Server 2012直接支持累积和。在早期版本中，您需要以不同的方式进行计算。我喜欢用相关子查询:

select row, itemcount,
       (select sum(itemcount)
        from t t2
        where t2.row <= t.row
       ) as cumsum
from t

Next, you need the first row that is bigger than the given value. For this, use a subquery and some arithmetic:

接下来，需要第一行大于给定值。为此，使用子查询和一些算术:

select t.row, t.itemcount
from (select row, itemcount,
             (select sum(itemcount)
              from t t2
              where t2.row <= t.row
             ) as cumsum
      from t
     ) t
where 75 > cumsum - itemcount and 75 <= cumsum;

The logic behind the where clause rather simple. The cumsum has to be greater than 75 the cutoff value. However, there can be multiple rows that meet this condition. You want the want where the previous value of cumsum is less than 75 -- which you get by subtracting the current itemcount.

where子句后面的逻辑相当简单。cumsum必须大于截止值的75。但是，可以有多个行满足这个条件。您想要的是前面的cumsum值小于75的值——这是通过减去当前的itemcount得到的。

#2

DECLARE @target int = 75;

DECLARE @t TABLE (rownum int, value int);
INSERT @t VALUES (1,60),(2,20),(3,7),(4,20);

WITH t_sum AS (
  SELECT
    rownum,
    value,
    SUM(value) OVER(ORDER BY rownum) AS r_total
  FROM @t
)
SELECT
  rownum,value
FROM t_sum
WHERE r_total - value < @target

#1