SQL Server 2012按季度计算累计总额，重复季度行显示0

当然可以在单条SELECT语句里实现你的需求！核心思路是结合窗口函数完成两个关键步骤：给每个季度内的行按日期排序编号，以及计算累计到当前季度的保费总额，最后通过CASE语句控制只在季度首行显示累计值。

最终查询语句 #

DECLARE @TempTable1 table (ID int, Date date, PolicyNumber varchar(100), Premium money)
INSERT INTO @TempTable1 values 
(1, '2018-01-01', 'Policy1', 100),
(2, '2018-02-08', 'Policy2', 200),
(3, '2018-04-15', 'Policy3', 300),
(4, '2018-05-31', 'Policy4', 150),
(5, '2018-07-10', 'Policy5', 250),
(6, '2018-11-23', 'Policy6', 350),
(7, '2018-12-05', 'Policy7', 330),
(8, '2019-01-09', 'Policy8', 140),
(9, '2019-06-18', 'Policy9', 225),
(10, '2019-06-28', 'Policy10', 145)

SELECT 
    ID, 
    Date, 
    PolicyNumber, 
    YEAR(Date) AS Year, 
    DATEPART(qq, Date) AS Quarter, 
    Premium,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY YEAR(Date), DATEPART(qq, Date) ORDER BY Date) = 1
        THEN SUM(SUM(Premium)) OVER (PARTITION BY YEAR(Date) ORDER BY DATEPART(qq, Date))
        ELSE 0
    END AS RunningTotalPremium
FROM @TempTable1
GROUP BY ID, Date, PolicyNumber, Premium, YEAR(Date), DATEPART(qq, Date)
ORDER BY Year, Quarter, Date;

关键部分解释 #

1. 行编号控制：ROW_NUMBER() OVER (PARTITION BY YEAR(Date), DATEPART(qq, Date) ORDER BY Date) 会给每一年每个季度内的行按日期排序，生成从1开始的编号。我们用这个编号判断当前行是否是该季度的第一行。
2. 累计总额计算：SUM(SUM(Premium)) OVER (PARTITION BY YEAR(Date) ORDER BY DATEPART(qq, Date)) 是嵌套的窗口函数：
   • 内层SUM(Premium)先按年度和季度分组计算每个季度的保费总和
   • 外层SUM(...)再按年度分区，按季度顺序累计这些季度总和，得到截至当前季度的累计保费
3. 显示逻辑：CASE语句判断如果是季度首行（编号=1），就显示累计总额，否则显示0。

查询结果示例 #

内容的提问来源于stack exchange，提问作者Serdia