sql-server - 如何在 SQL Server 中获取累积和(与过去的总和)

Question

我使用 SQL Server 2008。

在我的数据库中，我有一个表 items，其中包含以下示例数据:

id     |date      |title    |price
-----------------------------------
1      |20150310  |A        |100   
2      |20150201  |B        |25   
3      |20140812  |C        |225   
4      |20130406  |D        |110   
5      |20150607  |E        |645   
6      |20120410  |F        |450   
7      |20130501  |G        |325   
8      |20150208  |H        |175   

我想得到 20150101 之后 price 列的累计总和(加上过去的总和)。

事实上我想要这样的结果:

date      |title        |price    |cum_sum   |before_cum_sum
---------------------------------------------------------------
          |sum of past  |         |         |1110
20150201  |B            |25       |1135     |
20150208  |H            |175      |1310     |
20150310  |A            |100      |1410     |
20150607  |E            |645      |2055     |

如何在 SQL Server 中得到这样的结果？

Answer 1

OP 想要特定的输出 - 所以我遵守:

DECLARE @items TABLE ([date] DATETIME, title VARCHAR (100), price MONEY)
INSERT @items VALUES ('20150310','A',100), ('20150201','B',25 ), ('20140812','C',225), ('20130406','D',110),   ('20150607','E',645),   ('20120410','F',450),   ('20130501','G',325),   ('20150208','H',175)
;WITH p_cte AS (
  SELECT
    *
  , RowNo = ROW_NUMBER() OVER (ORDER BY DATE)
  FROM
    @items
),
p2_cte AS (
  SELECT
    p_cte.date
  , p_cte.title
  , p_cte.price
  , p_cte.RowNo
  , cum_sum = price
  , before_cum_sum = CONVERT (MONEY, 0)
  FROM
    p_cte
  WHERE p_cte.RowNo = 1
  UNION ALL
  SELECT
    cur.date
  , cur.title
  , cur.price
  , cur.RowNo
  , cum_sum = cur.price + prev.cum_sum
  , before_cum_sum = prev.cum_sum
  FROM
    p_cte cur
  INNER JOIN p2_cte prev ON
    prev.RowNo = cur.RowNo - 1
)
SELECT TOP 1
  date = CONVERT (DATETIME, NULL)
, title = 'sum of past'
, price = CONVERT (MONEY, NULL)
, rowno = CONVERT (INT, NULL)
, cum_sum = CONVERT (MONEY, NULL)
, p2_cte.before_cum_sum
FROM p2_cte WHERE date > '20150101'
UNION ALL
SELECT
  *
FROM p2_cte WHERE date > '20150101'

输出如下所示: