SQL查询末行求和时WITH ROLLUP放置位置及优化问题

• 在现有零件库存类查询的结果最后一行，追加Anzahl（即Count/计数）字段的汇总值
• 此前使用WITH ROLLUP实现过分组汇总，但当前查询嵌套了多组关联子查询，无法确定该语法的正确放置位置
• 需要对现有冗余的关联子查询写法做性能优化

原写法每返回一行零件记录，就要执行6~7次关联子查询，数据量大时性能极差；且直接在WHERE子句中引用SELECT层定义的Gesperrt别名，不符合SQL执行逻辑，会直接抛出语法错误；直接套WITH ROLLUP会因为关联子查询的逐行计算逻辑，导致汇总行计算错误。

优化思路是先把两张表的聚合逻辑提前做预聚合，消除逐行关联子查询，再基于预聚合结果计算各业务字段，最后通过WITH ROLLUP生成最后一行汇总，汇总行的零件号位置显示Anzahl作为标识：

WITH trans_agg AS (
    SELECT
        TeileNr,
        SUM(CASE WHEN Zustand = 1 THEN Anzahl ELSE 0 END) AS trans_z1_sum,
        SUM(CASE WHEN Zustand = 4 THEN Anzahl ELSE 0 END) AS trans_z4_sum,
        SUM(CASE WHEN Zustand = 6 THEN Anzahl ELSE 0 END) AS trans_z6_sum,
        MAX(CASE WHEN Zustand = 6 THEN Kommentar ELSE NULL END) AS z6_kommentar
    FROM tbl_Transaktion
    GROUP BY TeileNr
),
prod_agg AS (
    SELECT
        TeileNr,
        SUM(Stueckzahl_Prod) AS prod_sum
    FROM tbl_Produktion
    GROUP BY TeileNr
)
SELECT
    -- 汇总行时TeileNr位置显示'Anzahl'，普通行显示零件号
    CASE WHEN GROUPING(B.TeileNr) = 1 THEN 'Anzahl' ELSE B.TeileNr END AS TeileNr,
    SUM(trans_z1_sum - COALESCE(prod_sum,0)) AS Rohteile,
    SUM(COALESCE(prod_sum,0)) AS Fertigteile,
    SUM(trans_z4_sum - trans_z6_sum) AS Gesperrt,
    -- 汇总行不需要显示备注，普通行取对应备注
    CASE WHEN GROUPING(B.TeileNr) = 1 THEN NULL ELSE MAX(z6_kommentar) END AS Kommentar,
    SUM(
        trans_z1_sum - COALESCE(prod_sum,0) 
        + COALESCE(prod_sum,0) 
        + trans_z4_sum 
        - trans_z6_sum
    ) AS Gesamt
FROM tbl_TeileNr AS A 
INNER JOIN trans_agg AS B ON A.TeileNr = B.TeileNr
LEFT JOIN prod_agg AS C ON B.TeileNr = C.TeileNr
WHERE 
    (trans_z4_sum - trans_z6_sum) = 0 -- 替换原Gesperrt = false的过滤逻辑
    AND A.Produktgruppe = 1
GROUP BY B.TeileNr WITH ROLLUP
ORDER BY GROUPING(B.TeileNr), B.TeileNr;

• WITH ROLLUP直接加在GROUP BY子句的末尾即可，预聚合后的逻辑不会出现逐行计算导致的汇总错误
• 用GROUPING()函数判断当前行是否为ROLLUP生成的汇总行，汇总行将零件号列替换为Anzahl标识
• 提前用CTE对两张业务表做按零件号的一次性聚合，把原查询十几次子查询的逻辑压缩为2次表扫描聚合，性能提升非常明显
• 修正了原查询中WHERE子句引用SELECT别名的语法错误，用实际计算逻辑替换别名做过滤
• 用COALESCE()处理生产表无匹配记录时的空值问题，避免计算结果返回NULL
• 排序逻辑增加GROUPING(B.TeileNr)判断，保证汇总行固定在结果最后一行

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