sql - 计算运行总计时出错(前几个时期的累计)

Question

我有一个表，我们称它为 My_Table，它有一个 Created 日期时间列(在 SQL Server 中)，我试图提取一个报告，显示历史如何在特定时间按月将许多行添加到 My_Table。现在我知道我可以通过以下方式显示每个月添加的数量:

SELECT YEAR(MT.Created), MONTH(MT.Created), COUNT(*) AS [Total Added]
FROM My_Table MT
GROUP BY YEAR(MT.Created), MONTH(MT.Created)
ORDER BY YEAR(MT.Created), MONTH(MT.Created)

这会返回类似的东西:

YEAR    MONTH     Total Added
-----------------------------
2009    01        25
2009    02        127
2009    03        241

但是，我想获得给定时间段内的总列表大小(随便怎么称呼；运行总计、累计总和、历史报告):

   YEAR    MONTH     Total Size
   -----------------------------
-- 2008    12        325
   2009    01        350
   2009    02        477
   2009    03        718

我正在尝试这个:

SELECT YEAR(MT.Created)
    , MONTH(MT.Created)
    ,(
    SELECT COUNT(*) FROM My_Table MT_int
    WHERE MT_int.Created BETWEEN 
        CAST('2009/01/01' AS datetime)
        AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MT.Created)+1,0))
        -- the last day of the current month
        -- (Additional conditions can go here)
    ) AS [Total added this month]
FROM My_Table MT
WHERE MT.Created > CAST('2009/01/01' AS datetime)
GROUP BY YEAR(MT.Created), MONTH(MT.Created)
ORDER BY YEAR(MT.Created), MONTH(MT.Created)

但是，SQL Server 响应此错误:

Msg 8120, Level 16, State 1, Line 1
Column 'My_Table .Created' is invalid in the select list because 
it is not contained in either an aggregate function or the GROUP BY clause.

我只是知道我错过了一些明显的东西，但在走开并回来盯着它看了一会儿之后，我不知所措。因此，如果有人愿意指出我在地球上遗漏了什么(或指出更好的方法)，我将永远感激不已。

Answer 1

“运行”表示逐行。因此，一种方法是对前几个月求和并将其添加到当前月份。为了处理年份界限，您还需要每组的最小/最大日期。 CROSS APPLY 有点 RBAR，但清楚(呃？)发生了什么。

;WITH cTE AS
(
SELECT
     MIN(Created) AS FirstPerGroup,
     MAX(Created) AS LastPerGroup,
     YEAR(MT.Created) AS yr, MONTH(MT.Created) AS mth, COUNT(*) AS [Monthly Total Added]
FROM MY_Table MT
GROUP BY YEAR(MT.Created), MONTH(MT.Created)
)
SELECT
   C1.yr, c1.mth, SUM(C1.[Monthly Total Added]),
   ISNULL(PreviousTotal, 0) + SUM(C1.[Monthly Total Added]) AS RunningTotal
FROM
 cTE c1
 CROSS APPLY
 (SELECT SUM([Monthly Total Added]) AS PreviousTotal FROM cTE c2 WHERE c2.LastPerGroup < C1.FirstPerGroup) foo
GROUP BY
  C1.yr, c1.mth, PreviousTotal
ORDER BY
   C1.yr, c1.mth