如何用CASE WHEN子句实现月度统计值占当年该月总计的百分比

嘿，要把原来的各月计数改成当年该月总销量的百分比，我们可以先计算每个年份-月份组合的全局总销量，再和产品级的月销量做关联计算。这里用CTE（公共表表达式）来实现会比较清晰：

WITH MonthlyTotals AS (
    -- 先计算每个年份、每个月的总销量
    SELECT 
        YEAR(dateOrdered) AS Year,
        MONTH(dateOrdered) AS Month,
        COUNT(productId) AS MonthTotal
    FROM Table1
    GROUP BY YEAR(dateOrdered), MONTH(dateOrdered)
)
SELECT 
    t.productId,
    t.Year,
    -- 计算各月百分比，用NULLIF避免除以零的错误，保留两位小数示例
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 1 THEN t.productId END), 0) * 100.0 / NULLIF(mt1.MonthTotal, 0), 2) AS Jan,
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 2 THEN t.productId END), 0) * 100.0 / NULLIF(mt2.MonthTotal, 0), 2) AS Feb,
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 3 THEN t.productId END), 0) * 100.0 / NULLIF(mt3.MonthTotal, 0), 2) AS Mar,
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 4 THEN t.productId END), 0) * 100.0 / NULLIF(mt4.MonthTotal, 0), 2) AS Apr,
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 5 THEN t.productId END), 0) * 100.0 / NULLIF(mt5.MonthTotal, 0), 2) AS May,
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 6 THEN t.productId END), 0) * 100.0 / NULLIF(mt6.MonthTotal, 0), 2) AS Jun,
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 7 THEN t.productId END), 0) * 100.0 / NULLIF(mt7.MonthTotal, 0), 2) AS Jul,
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 8 THEN t.productId END), 0) * 100.0 / NULLIF(mt8.MonthTotal, 0), 2) AS Aug,
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 9 THEN t.productId END), 0) * 100.0 / NULLIF(mt9.MonthTotal, 0), 2) AS Sep,
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 10 THEN t.productId END), 0) * 100.0 / NULLIF(mt10.MonthTotal, 0), 2) AS Oct,
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 11 THEN t.productId END), 0) * 100.0 / NULLIF(mt11.MonthTotal, 0), 2) AS Nov,
    ROUND(ISNULL(COUNT(CASE WHEN MONTH(t.dateOrdered) = 12 THEN t.productId END), 0) * 100.0 / NULLIF(mt12.MonthTotal, 0), 2) AS Dec
FROM Table1 t
-- 每个月份关联对应的月度总计
LEFT JOIN MonthlyTotals mt1 ON mt1.Year = YEAR(t.dateOrdered) AND mt1.Month = 1
LEFT JOIN MonthlyTotals mt2 ON mt2.Year = YEAR(t.dateOrdered) AND mt2.Month = 2
LEFT JOIN MonthlyTotals mt3 ON mt3.Year = YEAR(t.dateOrdered) AND mt3.Month = 3
LEFT JOIN MonthlyTotals mt4 ON mt4.Year = YEAR(t.dateOrdered) AND mt4.Month = 4
LEFT JOIN MonthlyTotals mt5 ON mt5.Year = YEAR(t.dateOrdered) AND mt5.Month = 5
LEFT JOIN MonthlyTotals mt6 ON mt6.Year = YEAR(t.dateOrdered) AND mt6.Month = 6
LEFT JOIN MonthlyTotals mt7 ON mt7.Year = YEAR(t.dateOrdered) AND mt7.Month = 7
LEFT JOIN MonthlyTotals mt8 ON mt8.Year = YEAR(t.dateOrdered) AND mt8.Month = 8
LEFT JOIN MonthlyTotals mt9 ON mt9.Year = YEAR(t.dateOrdered) AND mt9.Month = 9
LEFT JOIN MonthlyTotals mt10 ON mt10.Year = YEAR(t.dateOrdered) AND mt10.Month = 10
LEFT JOIN MonthlyTotals mt11 ON mt11.Year = YEAR(t.dateOrdered) AND mt11.Month = 11
LEFT JOIN MonthlyTotals mt12 ON mt12.Year = YEAR(t.dateOrdered) AND mt12.Month = 12
GROUP BY t.productId, YEAR(t.dateOrdered),
         mt1.MonthTotal, mt2.MonthTotal, mt3.MonthTotal, mt4.MonthTotal,
         mt5.MonthTotal, mt6.MonthTotal, mt7.MonthTotal, mt8.MonthTotal,
         mt9.MonthTotal, mt10.MonthTotal, mt11.MonthTotal, mt12.MonthTotal
ORDER BY YEAR(t.dateOrdered), COUNT(t.productId) DESC;

关键说明： #

• MonthlyTotals CTE：先单独统计每个年份-月份的总销量，作为后续计算百分比的分母。
• 百分比计算：用产品当月的销量乘以100，再除以当月总销量，用ROUND()函数控制小数位数（这里示例保留2位）。
• 防除以零：用NULLIF(mt1.MonthTotal, 0)确保如果某个月没有总销量时，不会触发除以零的错误，此时结果会显示为NULL，如果想显示0，可以在外层再加ISNULL(..., 0)。
• 关联逻辑：通过LEFT JOIN把每个月份的总计数据关联到主查询，确保即使某个月没有总销量，产品的数据也能正常返回。

如果你想简化写法，也可以用窗口函数来实现，但上面的写法和你原有的查询结构保持一致，更直观易懂，也方便后续调整。

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