SQL（SSMS环境）如何对4个CASE语句的结果求和并参与行级运算

SQL Server不支持在同一SELECT层级直接引用刚定义的字段别名，以下两种方案均可在单条SQL内完成你的需求，无需拆分语句。

方案1：CTE/子查询封装中间计算结果 #

适合需要多次复用4个品类独立销量的场景：

WITH SalesCalc AS (
    SELECT
        -- 替换为你实际的分组字段，如日期、区域等
        sale_date,
        region,
        SUM(CASE WHEN category = '品类A' THEN sales_qty ELSE 0 END) AS cat_a_sales,
        SUM(CASE WHEN category = '品类B' THEN sales_qty ELSE 0 END) AS cat_b_sales,
        SUM(CASE WHEN category = '品类C' THEN sales_qty ELSE 0 END) AS cat_c_sales,
        SUM(CASE WHEN category = '品类D' THEN sales_qty ELSE 0 END) AS cat_d_sales,
        SUM(sales_qty) AS total_sales
    FROM your_sales_table
    -- 替换为你实际的分组字段
    GROUP BY sale_date, region
)
SELECT
    *,
    -- 第5列：其他品类销量
    total_sales - (cat_a_sales + cat_b_sales + cat_c_sales + cat_d_sales) AS other_cat_sales,
    -- 示例除法：特定品类总销量占总销量比例
    CAST((cat_a_sales + cat_b_sales + cat_c_sales + cat_d_sales) AS DECIMAL(18,2)) / NULLIF(total_sales, 0) AS target_cat_rate
FROM SalesCalc

方案2：CROSS APPLY计算中间值（无需嵌套） #

适合不想写嵌套语句的场景，无需重复定义CASE逻辑：

SELECT
    sale_date,
    region,
    SUM(CASE WHEN category = '品类A' THEN sales_qty ELSE 0 END) AS cat_a_sales,
    SUM(CASE WHEN category = '品类B' THEN sales_qty ELSE 0 END) AS cat_b_sales,
    SUM(CASE WHEN category = '品类C' THEN sales_qty ELSE 0 END) AS cat_c_sales,
    SUM(CASE WHEN category = '品类D' THEN sales_qty ELSE 0 END) AS cat_d_sales,
    SUM(sales_qty) AS total_sales,
    SUM(sales_qty) - t.target_total_sales AS other_cat_sales,
    CAST(t.target_total_sales AS DECIMAL(18,2)) / NULLIF(SUM(sales_qty), 0) AS target_cat_rate
FROM your_sales_table
CROSS APPLY (
    -- 直接计算4个特定品类的总销量，和4条CASE的计算结果完全等价
    SELECT SUM(CASE WHEN category IN ('品类A','品类B','品类C','品类D') THEN sales_qty ELSE 0 END) AS target_total_sales
) t
GROUP BY sale_date, region, t.target_total_sales

如果是行级别非聚合计算（直接对原始表每行数据计算，不需要分组求和），直接去掉所有SUM和GROUP BY语句即可。

注意事项 #

• 所有除法运算前必须加NULLIF(分母, 0)，避免分母为0时触发除零报错
• 涉及整数的除法需要先将字段转成DECIMAL/FLOAT类型，否则会触发整数截断，例如2/5会返回0而非0.4

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