在当前行中，将一列的前一行值添加到另一列。

Question

在当前行中，将一列的前一行值添加到另一列。

sqlsql-serversql-server-2008

4

你好，我在一张表格中有一个列是这样的

Amount 
1
2
3
4
5
6
7

我希望我的函数或存储过程的结果像这样：

output
--------

1
3
6
10
15
21
28

针对上述问题，请帮我提供一个解决方案。

- nifabregas

你有尝试过任何方法吗？请参阅如何提出一个好问题？ - huMpty duMpty

可能是重复的问题 https://dev59.com/-XRA5IYBdhLWcg3wsgHq - Milen

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- GarethD · Accepted Answer

您可以使用 OUTER APPLY：

CREATE TABLE #T (Amount INT);
INSERT #T (Amount) VALUES (1), (2), (3), (4), (5), (6), (7);

SELECT  T.Amount, T2.Amount
FROM    #T T
        OUTER APPLY
        (   SELECT  Amount = SUM(Amount)
            FROM    #T T2
            WHERE   T2.Amount <= T.Amount
        ) T2;

DROP TABLE #T;

或者一个相关子查询：

CREATE TABLE #T (Amount INT);
INSERT #T (Amount) VALUES (1), (2), (3), (4), (5), (6), (7);

SELECT  T.Amount, 
        (   SELECT  Amount = SUM(Amount)
            FROM    #T T2
            WHERE   T2.Amount <= T.Amount
        ) 
FROM    #T T

DROP TABLE #T;

两者应该产生相同的计划（在这种情况下，它们基本上是相同的，IO 是相同的）。

没错，减法。最终我还是想出了解决方案，接下来我会讲一下我是如何最终得到解决方案的，因为这并不像累加和那么直截了当。

首先，我只是写了一个查询，该查询完全符合逻辑，本质上是：

f(x) = x - f(x - 1);

通过复制并粘贴前一行的公式，我得到了：

SELECT  [1] = 1,
        [2] = 2 - 1,
        [3] = 3 - (2 - 1),
        [4] = 4 - (3 - (2 - 1)),
        [5] = 5 - (4 - (3 - (2 - 1))),
        [6] = 6 - (5 - (4 - (3 - (2 - 1)))),
        [7] = 7 - (6 - (5 - (4 - (3 - (2 - 1)))));

我随后展开了所有的括号，得到：

SELECT  [1] = 1,
        [2] = 2 - 1,
        [3] = 3 - 2 + 1,
        [4] = 4 - 3 + 2 - 1,
        [5] = 5 - 4 + 3 - 2 + 1,
        [6] = 6 - 5 + 4 - 3 + 2 - 1,
        [7] = 7 - 6 + 5 - 4 + 3 - 2 + 1;

正如您所看到的，随着下降，运算符在每个数值之间交替使用+和-（例如对于5，您会添加3，对于6，您会减去3，然后对于7，您会再次添加它）。

这意味着您需要找出每个值的位置，以确定是否要添加或减去它。因此，使用以下内容：

SELECT  T.Amount, 
        T2.RowNum,
        T2.Amount
FROM    #T T
        OUTER APPLY
        (   SELECT  Amount, RowNum = ROW_NUMBER() OVER(ORDER BY Amount DESC)
            FROM    #T T2
            WHERE   T2.Amount < T.Amount
        ) T2
WHERE   T.Amount IN (4, 5)

你最终得到：

Amount  RowNum  Amount
-------------------------
4       1       3
4       2       2
4       3       1
-------------------------
5       1       4
5       2       3
5       3       2
5       4       1

因此，记住这两个公式:

[4] = 4 - 3 + 2 - 1,
[5] = 5 - 4 + 3 - 2 + 1,

我们可以看到，在 RowNum 为奇数的情况下，我们需要减去第二个金额，在它为偶数时我们需要将其加上。我们不能在 SUM 函数中使用 ROW_NUMBER()，因此我们需要执行第二个聚合，最终查询如下：

SELECT  T.Amount, 
        Subtraction = T.Amount - SUM(ISNULL(T2.Amount, 0))
FROM    #T T
        OUTER APPLY
        (   SELECT  Amount = CASE WHEN ROW_NUMBER() OVER(ORDER BY Amount DESC) % 2 = 0 THEN -Amount ELSE Amount END
            FROM    #T T2
            WHERE   T2.Amount < T.Amount
        ) T2
GROUP BY T.Amount;

SQL Fiddle上的示例