如何只获得每行中添加一个字段构成所需数量的行

Question

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

我有一个查询，它返回一组行，显示分散在一个地方（物理上）的不同箱子中的物品数量。该列表按排序顺序排列。

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

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

row  item count
1       20
2       60
3        7
4       20

如果仍然需要某种运行总量或数量，并且可以计算剩余的项目，那也很好，但这可能太过分了。

Answer 1

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

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

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

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;

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

Answer 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