Oracle中用GROUPING SETS统计产品类别等维度标价总和的问题

原查询问题分析 #

1. CTE中的GROUPING SETS包含了空集合()，会生成全表总合计行，不符合你需要的两项统计需求
2. 后续对CTE结果再次分组统计，由于CTE已计算过sum(prod_list_price)，二次SUM(prod_list_price)会导致统计值错误
3. 字段引用混乱：CTE中统计列名为sum_all，但后续查询却引用不存在的prod_list_price

正确实现代码 #

SELECT 
    prod_category,
    prod_subcategory,
    supplier_id,
    SUM(prod_list_price) AS sum_prod_list_price,
    GROUPING_ID(prod_category, prod_subcategory, supplier_id) AS group_id
FROM products
GROUP BY GROUPING SETS (
    -- 按类别、子类别、供应商ID分组统计
    (prod_category, prod_subcategory, supplier_id),
    -- 仅按类别分组统计
    (prod_category)
)
ORDER BY prod_category, prod_subcategory, supplier_id;

代码说明 #

• GROUPING SETS直接指定你需要的两个分组集合，无需额外嵌套CTE
• GROUPING_ID字段可区分不同分组行：
  • group_id = 0：对应按prod_category, prod_subcategory, supplier_id分组的统计行
  • group_id = 3（二进制11，表示后两个字段被聚合）：对应仅按prod_category分组的统计行
• 若需要更友好的显示效果（标记类别合计行），可使用GROUPING函数优化：

SELECT 
    prod_category,
    CASE WHEN GROUPING(prod_subcategory) = 1 THEN '【类别合计】' ELSE prod_subcategory END AS prod_subcategory,
    CASE WHEN GROUPING(supplier_id) = 1 THEN NULL ELSE supplier_id END AS supplier_id,
    SUM(prod_list_price) AS sum_prod_list_price
FROM products
GROUP BY GROUPING SETS (
    (prod_category, prod_subcategory, supplier_id),
    (prod_category)
)
ORDER BY prod_category, GROUPING(prod_subcategory), prod_subcategory;

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