-
group by rollup(a,b)
該分組先做一次a和b的分組,再去掉b做一次a的分組,再去掉a做一次0的分組(相當月不分組)。總體邏輯就是分組條件一個一個的減少,直至沒有條件不分組為止
例如:統計公司每個部門的薪水綜合,部門編号以及公司所有薪水總和
SQL> select deptno,sum(sal) from emp group by rollup(deptno); DEPTNO SUM(SAL) ---------- ---------- 10 8750 20 10875 30 9400 29025
最後一行就顯示了所有薪水綜合。
例如:統計公司每個部門的每個職位的薪水總和,每個部門薪水總和,公司總薪水,顯示部門編号,職位,薪水總和。
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job); DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 DEPTNO JOB SUM(SAL) ---------- --------- ---------- 30 9400 29025 13 rows selected.
-
group by cube(a,b)
該分組類類似于group by 0
group by a
group by b
group by a,b
例如:統計公司每個部門的每個職位的薪水總和,每個部門薪水總和,每個職位薪水總和, 公司總薪水,顯示部門編号,職位,薪水總和。
SQL> select deptno,job,sum(sal) from emp group by cube(deptno,job); DEPTNO JOB SUM(SAL) ---------- --------- ---------- 29025 CLERK 4150 ANALYST 6000 MANAGER 8275 SALESMAN 5600 PRESIDENT 5000 10 8750 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 10875 DEPTNO JOB SUM(SAL) ---------- --------- ---------- 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 30 9400 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 18 rows selected.
-
grouping sets 分組組合
grouping sets(a,b)類似于:group by a
group by b
例如:統計公司每個部門薪水總和,每個職位薪水總和,以及顯示部門編号,職位,薪水總和。
SQL> select deptno,job from emp group by grouping sets(deptno,job); DEPTNO JOB ---------- --------- CLERK SALESMAN PRESIDENT MANAGER ANALYST 30 20 10 8 rows selected.
-
grouping() 分組表報告函數
grouping (a) 能顯示字段a是否被用作分組,0 是,1 否。
SQL> select deptno,job,grouping(deptno) from emp group by grouping sets(deptno,job); DEPTNO JOB GROUPING(DEPTNO) ---------- --------- ---------------- CLERK 1 SALESMAN 1 PRESIDENT 1 MANAGER 1 ANALYST 1 30 0 20 0 10 0 8 rows selected.
版權聲明:本文為CSDN部落客「weixin_33704234」的原創文章,遵循CC 4.0 BY-SA版權協定,轉載請附上原文出處連結及本聲明。
原文連結:https://blog.csdn.net/weixin_33704234/article/details/92509583