- 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.
转载于:https://my.oschina.net/wangzilong/blog/851876