T-SQL使用Pivot/条件聚合实现count与SUM分组透视查询

下面给出两种可直接运行的实现方案，均能完全匹配预期输出结果：

方案1：条件聚合（推荐，易维护、兼容性强） #

该写法通过CASE WHEN按阶段筛选值，直接分组聚合，适配所有SQL Server版本，后续新增阶段只需要追加对应判断行即可，逻辑直观。

-- 测试数据构造段，实际业务场景替换成你的源表名即可
WITH SourceData AS (
    SELECT EmpId, Stages, Amount
    FROM (VALUES
        (101,'Stg1',10.2),
        (101,'Stg2',10.22),
        (101,'Stg1',10.11),
        (101,'Stg3',6.21),
        (101,'Stg2',3.22),
        (102,'Stg1',3.23),
        (102,'Stg2',2.22),
        (102,'Stg3',1.22),
        (102,'Stg3',3.22)
    ) AS t(EmpId, Stages, Amount)
)
-- 核心统计逻辑
SELECT
    EmpId,
    SUM(CASE WHEN Stages = 'Stg1' THEN 1 ELSE 0 END) AS [Stg1-Count],
    SUM(CASE WHEN Stages = 'Stg1' THEN Amount ELSE 0 END) AS [Amount-stg1],
    SUM(CASE WHEN Stages = 'Stg2' THEN 1 ELSE 0 END) AS [Stg2-Count],
    SUM(CASE WHEN Stages = 'Stg2' THEN Amount ELSE 0 END) AS [Amount-stg2],
    SUM(CASE WHEN Stages = 'Stg3' THEN 1 ELSE 0 END) AS [Stg3-Count],
    SUM(CASE WHEN Stages = 'Stg3' THEN Amount ELSE 0 END) AS [Amount-stg3]
FROM SourceData
GROUP BY EmpId

执行后返回结果和预期完全一致：

EmpId Stg1-Count Amount-stg1 Stg2-Count Amount-stg2 Stg3-Count Amount-stg3
101 2 20.31 2 13.44 1 6.21
102 1 3.23 1 2.22 2 4.44

方案2：PIVOT透视实现 #

如果偏好PIVOT语法，可以先构造计数、金额两个维度的透视标识，再通过双透视完成统计，代码如下：

WITH SourceData AS (
    SELECT EmpId, Stages, Amount
    FROM (VALUES
        (101,'Stg1',10.2),
        (101,'Stg2',10.22),
        (101,'Stg1',10.11),
        (101,'Stg3',6.21),
        (101,'Stg2',3.22),
        (102,'Stg1',3.23),
        (102,'Stg2',2.22),
        (102,'Stg3',1.22),
        (102,'Stg3',3.22)
    ) AS t(EmpId, Stages, Amount)
),
PrePivot AS (
    SELECT
        EmpId,
        CONCAT('Cnt_',Stages) AS CountCol,
        CONCAT('Amt_',Stages) AS AmountCol,
        1 AS CountVal,
        Amount AS AmountVal
    FROM SourceData
)
SELECT
    p.EmpId,
    p.Cnt_Stg1 AS [Stg1-Count],
    p2.Amt_Stg1 AS [Amount-stg1],
    p.Cnt_Stg2 AS [Stg2-Count],
    p2.Amt_Stg2 AS [Amount-stg2],
    p.Cnt_Stg3 AS [Stg3-Count],
    p2.Amt_Stg3 AS [Amount-stg3]
FROM PrePivot
PIVOT (SUM(CountVal) FOR CountCol IN (Cnt_Stg1,Cnt_Stg2,Cnt_Stg3)) p
PIVOT (SUM(AmountVal) FOR AmountCol IN (Amt_Stg1,Amt_Stg2,Amt_Stg3)) p2

注意事项 #

• 如果源表中Amount字段存在NULL值，可按需调整CASE语句的ELSE逻辑，避免求和结果不符合预期
• 条件聚合写法的可维护性优于PIVOT写法，新增统计阶段时只需要追加对应两行CASE判断即可，不需要修改PIVOT的IN列表多处配置

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