--=============================================
-- SQL 基础--> ROLLUP与CUBE运算符实现数据汇总
--=============================================
在使用ROLLUP与CUBE运算符实现数据的汇总是,Oracle与SQL Server使用了不同的写法,但其实质是一样的,都遵循了SQL规范。
ROLLUP与CUBE运算符都是对GROUP BY 子句的扩展
SQL Server中的用法
GROUP BY col1,col2
WITH ROLLUP | CUBE ;
Oracle 中的用法
GROUP BY
ROLLUP | CUBE (col1,col2);
ROLLUP:为每个分组返回小计记录以及为所有分组返回总计记录
CUBE:返回列中所有组合的小计记录以及为所有分组返回总计记录
关于在SQL Sever中ROLLUP与CUBE运算符,请参照:ROLLUP 与CUBE 运算符的使用
一、演示ROLLUP 及CUBE 的使用
--使用常规的GROUP BY 实现数据的汇总
SQL> SELECT deptno,SUM(sal) FROM emp WHERE deptno IS NOT NULL
2 GROUP BY deptno;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750
--增加ROLLUP运算符实现对数据的汇总,增加了对DEPTNO列的总计
SQL> SELECT deptno,SUM(sal) FROM emp WHERE deptno IS NOT NULL
2 GROUP BY ROLLUP (deptno);
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
29025
--使用ROLLUP实现对多列数据进行汇总
--以下示例,先按deptno,job分组,并实现sum函数的聚合,接下来按从右向左的方向实现更高层次的聚合,如对同一个
--部门的作汇总,最后对所有部门作汇总。汇总层数为n+1,其中n为rollup中的列数
--因表emp存在deptno为空的记录,故以下所有演示中使用了where 子句过滤空值
SQL> SELECT deptno,job, SUM(sal) FROM emp WHERE deptno IS NOT NULL
2 GROUP BY ROLLUP (deptno,job)
3 ORDER BY deptno,job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
29025
--交换deptno,job列查看不同的列的顺序导致了不同的结果,原因是不同的分组产生了不同的结果
SQL> SELECT deptno,job, SUM(sal) FROM emp WHERE deptno IS NOT NULL
2 GROUP BY ROLLUP (job,deptno)
3 ORDER BY job,deptno;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 ANALYST 6000
ANALYST 6000
10 CLERK 1300
20 CLERK 1900
30 CLERK 950
CLERK 4150
10 MANAGER 2450
20 MANAGER 2975
30 MANAGER 2850
MANAGER 8275
10 PRESIDENT 5000
DEPTNO JOB SUM(SAL)
---------- --------- ----------
PRESIDENT 5000
30 SALESMAN 5600
SALESMAN 5600
29025
--ROLLUP ,CUBE可以配合不同的聚合函数来使用
SQL> SELECT deptno,job, SUM(sal),ROUND(AVG(sal),2) FROM emp WHERE deptno IS NOT NULL
2 GROUP BY ROLLUP (deptno,job)
3 ORDER BY deptno,job;
DEPTNO JOB SUM(SAL) ROUND(AVG(SAL),2)
---------- --------- ---------- -----------------
10 CLERK 1300 1300
10 MANAGER 2450 2450
10 PRESIDENT 5000 5000
10 8750 2916.67
20 ANALYST 6000 3000
20 CLERK 1900 950
20 MANAGER 2975 2975
20 10875 2175
30 CLERK 950 950
30 MANAGER 2850 2850
30 SALESMAN 5600 1400
DEPTNO JOB SUM(SAL) ROUND(AVG(SAL),2)
---------- --------- ---------- -----------------
30 9400 1566.67
29025 2073.21
13 rows selected.
--使用CUBE子句实现对数据的汇总
--从结果集中可以看出CUBE对不同的维度也实现了数据汇总,本例中多出的列即为不同的JOB也产生了汇总数据
SQL> SELECT deptno,job, SUM(sal) FROM emp WHERE deptno IS NOT NULL
2 GROUP BY CUBE (deptno,job)
3 ORDER BY deptno,job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025
18 rows selected.
二、使用GROUPING函数处理汇总结果中的空值
GROUPING函数仅在使用ROLLUP和CUBE查询中使用,可以接受一列,其结果返回为或者,如果列值为空,则返回,否则返回
--单列使用GROUPING函数
SQL> SELECT GROUPING(deptno),deptno, SUM(sal) FROM emp WHERE deptno IS NOT NULL
2 GROUP BY ROLLUP(deptno)
3 ORDER BY deptno;
GROUPING(DEPTNO) DEPTNO SUM(SAL)
---------------- ---------- ----------
0 10 8750
0 20 10875
0 30 9400
1 29025
--使用CASE子句转换GROUPING函数的返回值
SQL> SELECT CASE GROUPING(deptno) WHEN 1 THEN 'All Deptno' ELSE TO_CHAR(deptno) END AS deptnumber, SUM(sal)
2 FROM emp WHERE deptno IS NOT NULL
3 GROUP BY ROLLUP(deptno)
4 ORDER BY deptno;
DEPTNUMBER &nb
再分享一下我老师大神的人工智能教程吧。零基础!通俗易懂!风趣幽默!还带黄段子!希望你也加入到我们人工智能的队伍中来!https://blog.csdn.net/jiangjunshow