sql - 累积过去 12 个月的月度总数

Question

CurrentMonth = Month(CurrentDate)
CurrentYear = Year(CurrentDate)

    SQL = "SELECT Spent, MONTH(Date) AS InvMonth, YEAR(Date) As InvYear FROM Invoices WHERE YEAR(Date) = '" & CurrentYear & "' AND MONTH(Date) = '" & CurrentMonth & "'"
    RecordSet.Open SQL, Connection, adOpenStatic, adLockOptimistic, adCmdText
    Do Until RecordSet.EOF
        MTotal(i) = MTotal(i) + RecordSet.Fields("Spent")
        RecordSet.MoveNext
    Loop
    RecordSet.Close

这是我目前必须建立给定月份总支出的代码。我希望扩展它以检索过去 12 个月的每月总计。

我认为这样做的方法是通过 CurrentMonth 值向后循环，如果 CurrentMonth 值达到 0，则将 CurrentYear 的值回滚 1。使用循环变量 (i) 构建一个包含 12 个值的数组: MTotal()

大家怎么看？

Answer 1

分组应该让你上路。

SELECT TOP 12
  SUM(Spent) AS Spent
  , MONTH(Date) AS InvMonth
  , YEAR(Date) AS InvYear
FROM
  Invoices
GROUP BY
  YEAR(Date), MONTH(Date)
WHERE DATEDIFF(mm, Date, GETDATE(()) < 12

---

Josh 的 DATEDIFF 是比我原来的 TOP 和 ORDER BY 更好的解决方案