如何通过Grouping Sets或Rollup实现SQL查询的多列总计？

原查询通过GROUPING SETS实现了按维度分组统计和总计，但总计行（Dimensions为NULL）的Available Size和Utilized Percentage字段为空。这是因为总计行没有对应的T0.MinLevel单个值，需要调整计算逻辑，分别针对分组行和总计行做不同的计算：

修改后的SQL查询 #

SELECT 
    CASE WHEN GROUPING(T0.Descr) = 1 THEN '总计' ELSE T0.Descr END [Dimensions], 
    COUNT(DISTINCT T0.BinCode) [Total Occupies], 
    -- 分组行取单个MinLevel，总计行求和所有可用容量
    CASE WHEN GROUPING(T0.Descr) = 1 
         THEN SUM(CAST(T0.MinLevel AS INT)) 
         ELSE CAST(T0.MinLevel AS INT) 
    END [Available Size], 
    -- 分组行计算当前组利用率，总计行计算整体利用率
    FORMAT(
        CASE WHEN GROUPING(T0.Descr) = 1 
             THEN COUNT(DISTINCT T0.BinCode) * 1.0 / NULLIF(SUM(CAST(T0.MinLevel AS INT)), 0) 
             ELSE COUNT(DISTINCT T0.BinCode) * 1.0 / NULLIF(T0.MinLevel, 0) 
        END, 
        '#.#%'
    ) [Utilized Percentage]  
FROM 
    [OBIN] T0 
LEFT JOIN 
    [OIBQ] T1 ON T0.WhsCode = T1.WhsCode 
              AND T0.AbsEntry = T1.BinAbs 
WHERE 
    t1.OnHandQty > 0 
    AND T0.Descr IS NOT NULL
GROUP BY 
    GROUPING SETS ((), (T0.Descr, T0.MinLevel))

关键调整说明 #

1. Available Size计算：

   • 当GROUPING(T0.Descr) = 1时，说明是总计行，用SUM(CAST(T0.MinLevel AS INT))求和所有分组的可用容量
   • 分组行保持原逻辑，取当前组的MinLevel转换为整数

2. Utilized Percentage计算：

   • 总计行用总占用数（COUNT(DISTINCT T0.BinCode)）除以总可用容量（SUM(CAST(T0.MinLevel AS INT))）
   • 分组行保持原逻辑，用当前组的占用数除以当前组的MinLevel
   • 使用NULLIF函数处理除数为0的情况，避免出现除以0的错误

3. 维度列优化：

   • 给总计行的Dimensions列显示“总计”，替代默认的NULL，更直观

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