时间戳

首页 > 解决方案 > SUM 列与上一列

sql - SUM 列与上一列

问题描述

我有下表：

+-----------+-------------------------+-----------+--------------+-----------------------+-------------+
| InvoiceId |       InvoiceDate       | ClientId | TotalPayment | CurrentInvoicePoints  | TotalPoints |
+-----------+-------------------------+-----------+--------------+-----------------------+-------------+
|         1 | 2016-09-28 00:00:00.000 |        10 |      2050.14 |                   136 | NULL        |
|         9 | 2016-12-28 00:00:00.000 |        10 |       772.31 |                    58 | NULL        |
|        25 | 2017-02-11 00:00:00.000 |        10 |       168.51 |                    17 | NULL        |
|        32 | 2017-02-21 00:00:00.000 |        20 |       758.27 |                    80 | NULL        |
|        24 | 2017-02-08 00:00:00.000 |        20 |      4493.45 |                   718 | NULL        |
|         8 | 2016-10-08 00:00:00.000 |        20 |      7049.08 |                   483 | NULL        |
+-----------+-------------------------+-----------+--------------+-----------------------+-------------+

每张发票都有多个点数： [CurrentInvoicePoints]
在 [TotalPoints] 我有所有点的总和（[CurrentInvoicePoints] + 以前的 [TotalPoints]）
示例：如果来自 clientId 10 的第一张发票有 136 点，则下一张将有 136 + [CurrentInvoicePoints] 等等。

因此结果应如下所示：

+-----------+-------------------------+-----------+--------------+-----------------------+-------------+
| InvoiceId |       InvoiceDate       | PartnerId | TotalPayment | CurrentInvoicePoints  | TotalPoints |
+-----------+-------------------------+-----------+--------------+-----------------------+-------------+
|         1 | 2016-09-28 00:00:00.000 |        10 |      2050.14 |                   136 |         136 |  =136 (first invoice for clientId 10)
|         9 | 2016-12-28 00:00:00.000 |        10 |       772.31 |                    58 |         194 |  =136+58
|        25 | 2017-02-11 00:00:00.000 |        10 |       168.51 |                    17 |         211 |  =194+17
|        32 | 2017-02-21 00:00:00.000 |        20 |       758.27 |                    80 |          80 |  =80 (first invoice for clientId 20)
|        24 | 2017-02-08 00:00:00.000 |        20 |      4493.45 |                   718 |         798 |  =80+718
|         8 | 2016-10-08 00:00:00.000 |        20 |      7049.08 |                   483 |        1281 |  =718+483
+-----------+-------------------------+-----------+--------------+-----------------------+-------------+

需要一些帮助，希望我会在找到方法后立即发布查询

标签： sqlsql-serverselectsumwindow-functions

解决方案

您正在描述一个窗口总和：

select
    t.*,
    sum(CurrentInvoicePoints) 
        over(partition by PartnerId order by InvoiceDate) as TotalPoints
from mytable t

您不需要实际存储此派生值。但如果你真的想要一个update语句，那么你可以将查询转换为可更新的 cte：

with cte as (
    select
        totalPoints,
        sum(CurrentInvoicePoints) 
            over(partition by PartnerId order by InvoiceDate) as NewTotalPoints
    from mytable
)
update cte set TotalPoints = NewTotalPoints

推荐阅读