如何用Case When替代变量与IF ELSE改写SQL表面积计算代码？

我有一段用于计算产品表面积并输出对应数值的SQL代码，希望用CASE WHEN语句替换原有的变量和IF ELSE逻辑，但尝试的写法中TOP(1)和GROUP BY无法正常配合工作。

原代码 #

DECLARE @test DECIMAL(10,2) 

SELECT
    @test = ([WIDTH] / 1000) * ([HEIGHT] / 1000)
FROM
    MAIN.SYSADM.[TABLE]
WHERE
    ID = 854037
    AND POS_NR = 1 


IF (@test > 1)
    BEGIN
    (
    SELECT
        TOP(1)
        360 / SUM(CAST([QTY] AS INT))
    FROM
        MAIN.SYSADM.[TABLE]
    WHERE
        ID = 854037
        AND POS_NR = 1 
        AND BOM_PRODUKT NOT IN (52101,52102,52006,52007,52003,52005,52008,53201,53102)
        AND (
            STL_PRODGRP IN (26,27,28,30,33,35,412,413,415,425,426,427)
            OR BOM_PRODUKT = 50002
        )
    GROUP BY
        BOM_NODE
    )
    END

ELSE
    BEGIN
    (
    SELECT
        TOP(1)
        180 / SUM(CAST([QTY] AS INT))
    FROM
        MAIN.SYSADM.[TABLE]
    WHERE
        ID = 854037
        AND POS_NR = 1 
        AND BOM_PRODUKT NOT IN (52101,52102,52006,52007,52003,52005,52008,53201,53102)
        AND (
            STL_PRODGRP IN (26,27,28,30,33,35,412,413,415,425,426,427) 
            OR BOM_PRODUKT = 50002
        )
    GROUP BY
        BOM_NODE
    ) 
    END

尝试的代码 #

SELECT
    (
    SELECT
        TOP(1)
        (
        CASE
            WHEN CAST(([WIDTH] / 1000) * ([HEIGHT] / 1000) AS DECIMAL(10,2)) > 1
            THEN 360 / SUM(CAST([QTY] AS INT))
            ELSE 180 / SUM(CAST([QTY] AS INT))
        END
        )
    )
FROM
    MAIN.SYSADM.[TABLE]
WHERE
    [ID] = 854037
    AND POS_NR = 1 
    AND BOM_PRODUKT NOT IN (52101,52102,52006,52007,52003,52005,52008,53201,53102)
    AND (
        STL_PRODGRP IN (26,27,28,30,33,35,412,413,415,425,426,427)
        OR BOM_PRODUKT = 50002
    )
GROUP BY
    BOM_NODE  

问题原因与解决方案 #

问题分析 #

尝试的写法存在两个核心问题：

1. 内层子查询使用TOP(1)但外层已按BOM_NODE分组，逻辑冲突，导致结果不符合预期；
2. CASE WHEN中引用的WIDTH和HEIGHT未包含在GROUP BY中（也不应该包含，因为表面积是基于指定ID和POS_NR的单一行数据），违反SQL聚合规则，同时逻辑上也无法正确关联分组数据与表面积值。

正确改写代码 #

通过CTE先获取目标表面积值，再在主查询中根据该值用CASE WHEN选择计算逻辑，同时保留TOP(1)和GROUP BY的合理逻辑：

WITH SurfaceArea AS (
    -- 提前获取指定条件的产品表面积
    SELECT CAST(([WIDTH]/1000)*([HEIGHT]/1000) AS DECIMAL(10,2)) AS Area
    FROM MAIN.SYSADM.[TABLE]
    WHERE ID = 854037 AND POS_NR = 1
)
SELECT TOP(1)
    -- 根据表面积判断使用360还是180作为分子
    CASE WHEN (SELECT Area FROM SurfaceArea) > 1 
         THEN 360 / SUM(CAST([QTY] AS INT))
         ELSE 180 / SUM(CAST([QTY] AS INT))
    END AS CalculatedValue
FROM MAIN.SYSADM.[TABLE]
WHERE ID = 854037
  AND POS_NR = 1
  AND BOM_PRODUKT NOT IN (52101,52102,52006,52007,52003,52005,52008,53201,53102)
  AND (STL_PRODGRP IN (26,27,28,30,33,35,412,413,415,425,426,427) OR BOM_PRODUKT = 50002)
GROUP BY BOM_NODE
-- 若需要固定排序，可添加ORDER BY子句，原代码未指定，此处保留原逻辑

逻辑说明 #

• 用CTESurfaceArea单独获取目标产品的表面积，确保值唯一且正确；
• 主查询按BOM_NODE分组计算QTY总和，通过CASE WHEN根据表面积动态选择计算分子；
• TOP(1)作用于分组后的结果集，符合原代码的逻辑需求。

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