如何修改SQL Pivot查询，新增按Name分组的CountFailed占比列

嘿，这事儿很简单！你只需要把按Name分组计算的失败占比和原Pivot的结果关联起来就行，我给你两种实用的实现方式：

方法一：使用CTE（可读性更强） #

这种方式把Pivot结果和失败占比分开来计算，逻辑清晰，后期维护也方便：

WITH PivotData AS (
    -- 原Pivot查询部分
    SELECT [Name], [01], [02], [03] 
    from ( 
        SELECT [Name], FORMAT([Date], 'MM') as [NumMonth], SUM([Count]) as [Total] 
        from #tmptmp 
        group by FORMAT([Date], 'MM'), [Name] 
    ) t 
    PIVOT ( 
        SUM([Total]) FOR [NumMonth] in ([01], [02], [03]) 
    ) as pivotTable
),
FailureRates AS (
    -- 单独计算每个Name的失败占比
    SELECT 
        [Name],
        ROUND(SUM([CountFailed]) * 100.0 / SUM([Count]), 2) AS [% Failed]
    FROM #tmptmp
    GROUP BY [Name]
)
-- 关联两个结果集得到最终数据
SELECT 
    pd.[Name],
    pd.[01],
    pd.[02],
    pd.[03],
    fr.[% Failed]
FROM PivotData pd
JOIN FailureRates fr ON pd.[Name] = fr.[Name]
ORDER BY pd.[Name];

方法二：使用关联子查询（更紧凑） #

如果不想用CTE，也可以直接在原查询的外层用关联子查询计算失败占比，写法更简洁：

SELECT 
    pt.[Name],
    pt.[01],
    pt.[02],
    pt.[03],
    -- 通过子查询获取当前Name的失败占比
    ROUND(
        (SELECT SUM([CountFailed]) FROM #tmptmp t WHERE t.[Name] = pt.[Name]) * 100.0 
        / (SELECT SUM([Count]) FROM #tmptmp t WHERE t.[Name] = pt.[Name]), 
        2
    ) AS [% Failed]
FROM (
    -- 原Pivot查询部分
    SELECT [Name], [01], [02], [03] 
    from ( 
        SELECT [Name], FORMAT([Date], 'MM') as [NumMonth], SUM([Count]) as [Total] 
        from #tmptmp 
        group by FORMAT([Date], 'MM'), [Name] 
    ) t 
    PIVOT ( 
        SUM([Total]) FOR [NumMonth] in ([01], [02], [03]) 
    ) as pivotTable
) pt
ORDER BY pt.[Name];

关键细节提醒 #

• 计算占比时一定要用* 100.0把整数转换成浮点数，避免SQL的整数除法问题（比如直接写6/28会得到0，转成浮点数后才能得到正确的0.2142...）
• 用ROUND(..., 2)可以把结果保留两位小数，和你预期的格式完全匹配

运行上面任意一种查询，都能得到你想要的结果：

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