SQL Server：使用CTE按年份分组数据（附示例数据）

没问题，我来帮你搞定在SQL Server里用CTE按年份分组汇总数据的需求。咱们先从创建测试用的临时表开始，把示例数据插进去：

-- 创建临时表
CREATE TABLE #temp (
    dateRef DATE,
    a FLOAT,
    b FLOAT,
    c FLOAT
);

-- 插入示例数据
INSERT INTO #temp (dateRef, a, b, c) VALUES ('20050101',1000,500,0);
INSERT INTO #temp (dateRef, a, b, c) VALUES ('20050201',1000,1000,0);
INSERT INTO #temp (dateRef, a, b, c) VALUES ('20050301',4000,4000,3000);
INSERT INTO #temp (dateRef, a, b, c) VALUES ('20050401',2000,2000,1000);
INSERT INTO #temp (dateRef, a, b, c) VALUES ('20050501',1000,2000,0);
INSERT INTO #temp (dateRef, a, b, c) VALUES ('20050601',2000,2000,0);
-- 可以继续插入更多不同年份的数据来测试

接下来就可以用CTE（公共表表达式）来实现按年份分组的汇总了。这里我们会先提取每条记录的年份，再对每个年份的a、b、c字段分别求和：

WITH YearlySummary AS (
    SELECT
        YEAR(dateRef) AS [Year], -- 提取dateRef字段的年份
        SUM(a) AS TotalA,       -- 汇总每年的a字段总和
        SUM(b) AS TotalB,       -- 汇总每年的b字段总和
        SUM(c) AS TotalC        -- 汇总每年的c字段总和
    FROM #temp
    GROUP BY YEAR(dateRef)      -- 按年份分组
)
-- 查询CTE得到最终结果
SELECT * FROM YearlySummary
ORDER BY [Year]; -- 按年份排序，让结果更直观

代码说明 #

• YearlySummary是我们定义的CTE名称，你可以根据需求改成更贴合业务的名字；
• YEAR(dateRef)函数用来从日期字段中提取年份，这是分组的关键依据；
• SUM()聚合函数用来计算每个分组内对应字段的总和，如果你需要其他统计（比如平均值、最大值），把SUM()换成AVG()、MAX()即可；
• 最后通过SELECT * FROM YearlySummary来获取分组后的汇总数据，加上ORDER BY [Year]可以让结果按年份升序排列。

如果你的需求是要同时展示明细数据和年度汇总，还可以用CTE结合窗口函数来实现，比如：

WITH YearlyDetails AS (
    SELECT
        dateRef,
        a,
        b,
        c,
        YEAR(dateRef) AS [Year],
        SUM(a) OVER (PARTITION BY YEAR(dateRef)) AS YearlyTotalA,
        SUM(b) OVER (PARTITION BY YEAR(dateRef)) AS YearlyTotalB,
        SUM(c) OVER (PARTITION BY YEAR(dateRef)) AS YearlyTotalC
    FROM #temp
)
SELECT * FROM YearlyDetails
ORDER BY [Year], dateRef;

这个写法会保留每条明细记录，同时在每行后面附上对应年份的汇总值，适合需要同时查看明细和汇总的场景。

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