GROUP BY CUBE

最新推荐文章于 2022-10-24 14:24:05 发布

原创最新推荐文章于 2022-10-24 14:24:05 发布 · 621 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

SQL 专栏收录该内容

48 篇文章

订阅专栏

本文通过一个具体的SQL查询案例介绍了如何使用立方分组（GROUP BY CUBE）来汇总不同部门和职位的员工数量。该查询展示了如何跨多个维度进行数据聚合，以方便进行多角度的数据分析。

select department_id, job_id
, count(department_id) headcount
from employees

group by cube(department_id, job_id);

SQL>   select department_id, job_id
2   , count(department_id) headcount
3   from employees
4   group by cube(department_id, job_id);

DEPARTMENT_ID JOB_ID      HEADCOUNT
------------- ---------- ----------
                                  0
                                106
              AD_VP               2
              AC_MGR              1
              FI_MGR              1
              HR_REP              1
              MK_MAN              1
              MK_REP              1
              PR_REP              1
              PU_MAN              1
              SA_MAN              5

DEPARTMENT_ID JOB_ID      HEADCOUNT
------------- ---------- ----------
              SA_REP              0
              SA_REP             29
              ST_MAN              5
              AD_ASST             1
              AD_PRES             1
              IT_PROG             5
              PU_CLERK            5
              SH_CLERK           20
              ST_CLERK           20
              AC_ACCOUNT          1
              FI_ACCOUNT          5

DEPARTMENT_ID JOB_ID      HEADCOUNT
------------- ---------- ----------
           10                     1
           10 AD_ASST             1
           20                     2
           20 MK_MAN              1
           20 MK_REP              1
           30                     6
           30 PU_MAN              1
           30 PU_CLERK            5
           40                     1
           40 HR_REP              1
           50                    45

DEPARTMENT_ID JOB_ID      HEADCOUNT
------------- ---------- ----------
           50 ST_MAN              5
           50 SH_CLERK           20
           50 ST_CLERK           20
           60                     5
           60 IT_PROG             5
           70                     1
           70 PR_REP              1
           80                    34
           80 SA_MAN              5
           80 SA_REP             29
           90                     3

DEPARTMENT_ID JOB_ID      HEADCOUNT
------------- ---------- ----------
           90 AD_VP               2
           90 AD_PRES             1
          100                     6
          100 FI_MGR              1
          100 FI_ACCOUNT          5
          110                     2
          110 AC_MGR              1
          110 AC_ACCOUNT          1

52 rows selected.