与上一列求和

watbbzwu 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(283)

我有下表：

+-----------+-------------------------+-----------+--------------+-----------------------+-------------+
| 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
+-----------+-------------------------+-----------+--------------+-----------------------+-------------+

需要一些帮助，希望我能在找到方法后尽快发布查询

sql window-functions sql-server sum select

来源：https://stackoverflow.com/questions/62505204/sum-column-with-previous-column

1条答案

按热度按时间

s1ag04yj1#

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

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

赞(0）回复(0）举报 2021-07-24

我来回答

与上一列求和

1条答案

相关问题

热门标签

最新问答