sql - T-SQL 查询以汇总每年每月的总金额以及迄今为止的累计金额

Question

我有一个捕获每个销售事务的数据库表：

Transactions 
(
    ID INT, 
    TransactionDate DATETIME, 
    SalesAmount MONEY
)

我想编写一个返回报告的T-SQL查询（下面的快照示例）。第一列列出了月份，下一列列出了一年内每月的总销售额，最后一列列出了该年截至本月的累计金额。仅适用于 2018 年。

有什么想法或解决方案吗？谢谢你。

标签： sqlsql-servertsql

Answer 1

试试这个：

;with cte as
( 
   Select 
   YEAR(TransactionDate) as [Year],
   MONTH(TransactionDate) as [Month],  
   SUM (SalesAmount) as [MonthlySales], 
   DATEPART(m, TransactionDate) as [MonthNumber] 
   from Transactions 
   group by YEAR(TransactionDate), MONTH(TransactionDate)
) 
select 
a.[Month], a.MonthlySales as [MonthlySales 2018], SUM(b.MonthlySales) as [Cumulative 2018]
from cte a inner join cte b on a.MonthNumber  >= b.MonthNumber 
WHERE (a.[Year]) = 2018   AND  (b.[Year]) = 2018  
group by a.[Month], a.MonthlySales
ORDER by a.[Month]