ROLLUP, CUBE, GROUPING Functions and GROUPING SETS

yan han

已于 2025-01-21 17:58:21 修改

阅读量664

点赞数 28

分类专栏： ORACLE SQL 文章标签： oracle sql

于 2025-01-20 16:57:57 首次发布

本文链接：https://blog.csdn.net/weixin_44875522/article/details/145265905

版权

ORACLE SQL 专栏收录该内容

3 篇文章

订阅专栏

ORACLE-BASE - ROLLUP, CUBE, GROUPING Functions and GROUPING SETS

看不懂的可以来问

-------------------------------------------------------------------------------------------------------------------------------

完了，我也看不懂了

思考一下几个SQL 的result sets:

SELECT fact_1_id,
       fact_2_id,
       fact_3_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id, fact_3_id) AS grouping_id
FROM   dimension_tab
GROUP BY GROUPING SETS((fact_1_id, fact_2_id), (fact_1_id, fact_3_id),())
ORDER BY fact_1_id, fact_2_id, fact_3_id;


SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id) AS grouping_id
FROM   dimension_tab
GROUP BY rollup((fact_1_id,fact_2_id),fact_1_id)
ORDER BY fact_1_id, fact_2_id;


SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id) AS grouping_id
FROM   dimension_tab
GROUP BY GROUPING SETS((fact_1_id, fact_2_id)),rollup(fact_1_id)
ORDER BY fact_1_id, fact_2_id;