group by cube 用法

最新推荐文章于 2025-06-19 14:07:17 发布

原创最新推荐文章于 2025-06-19 14:07:17 发布 · 3.7k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#group by cube

oracle 专栏收录该内容

3 篇文章

订阅专栏

本文展示了如何使用CUBE分组在SQL中对数据进行聚合，包括完整分组和部分分组，通过具体示例阐述了CUBE分组在不同维度组合上的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/*GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)， 
最后对全表进行GROUP BY操作。 */
--构造环境
drop table dept purge;
drop table emp purge;
create table dept as select * from scott.dept;
create table emp  as select * from scott.emp;

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

set term off
set heading on
set verify off
set feedback off
set linesize 2000
set pagesize 30000
set long 999999999
set longchunksize 999999
set autotrace off


-- CUBE分组
SELECT  a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b 
WHERE a.deptno = b.deptno
GROUP  BY CUBE(a.dname,b.job);

DNAME          JOB          SUM_SAL
-------------- --------- ----------
                              29025
               CLERK           4150
               ANALYST         6000
               MANAGER         8275
               SALESMAN        5600
               PRESIDENT       5000
SALES                          9400
SALES          CLERK            950
SALES          MANAGER         2850
SALES          SALESMAN        5600
RESEARCH                      10875
RESEARCH       CLERK           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
ACCOUNTING                     8750
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000


----部分CUBE分组
SELECT  a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b 
WHERE a.deptno = b.deptno
GROUP  BY a.dname,CUBE(b.job);

DNAME          JOB          SUM_SAL
-------------- --------- ----------
SALES                          9400
SALES          CLERK            950
SALES          MANAGER         2850
SALES          SALESMAN        5600
RESEARCH                      10875
RESEARCH       CLERK           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
ACCOUNTING                     8750
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000