时间戳

首页 > 解决方案 > Sum values of two columns and display in next row

sql - Sum values of two columns and display in next row

问题描述

I have a table which looks as below:

select * from #tempTable

    RN  Qty
    0   30
    1   -3
    2   -2
    3   8

Using the below query I was able to do as below:

WITH CTE AS
(SELECT ROW_NUMBER()OVER(ORDER BY RN) AS RN, Qty
FROM #tempTable)

SELECT T1.RN,T1.Qty StartingQty,T2.Qty as ChangedQty, (T1.Qty+T2.Qty) as TotalQty
FROM CTE T1 LEFT JOIN
     CTE T2 ON T1.RN=(T2.RN-1) where T2.Qty IS not null

This would result as below:

    RN  StartingQty ChangedQty  TotalQty
    1    30            -3         27
    2    -3            -2         -5
    3    -2             8          6

The result should look as below and not sure how to achieve this:

    RN  StartingQty ChangedQty  TotalQty
    1    30            -3         27
    2    27            -2         25
    3    25             8         33

Any leads would be helpful as of how to achieve this.

标签： sqlsql-serversumwindow-functions

解决方案

您可以尝试使用LEAD()和 windowed的方法SUM()：

SELECT *
FROM (
   SELECT
      RN,
      SUM(Qty) OVER (ORDER BY RN) AS StartingQty,
      LEAD(Qty) OVER (ORDER BY RN) AS ChangedQty,
      SUM(Qty) OVER (ORDER BY RN) + LEAD(Qty) OVER (ORDER BY RN) AS TotalQty
   FROM (VALUES
      (0, 30),
      (1, -3),
      (2, -2),
      (3, 8)
   ) v (RN, Qty)
) t
WHERE ChangedQty IS NOT NULL

结果：

RN  StartingQty ChangedQty  TotalQty
0            30         -3        27
1            27         -2        25
2            25          8        33

推荐阅读