Oracle高级查询,GROUP BY
来源:互联网 发布:复杂网络中的幂律分布 编辑:程序博客网 时间:2024/04/30 21:45
为了方便大家学习和测试,所有的例子都是在Oracle自带用户Scott下建立的。
现在客户的需求是统计部门中每种工作的工资总额,最后还需要统计所有人的工资总数,相信这样的需求对大家来说还是比较简单的,很快就能写出SQL语句,如下:
- select deptno, job, sum(sal)
- from scott.emp
- group by deptno, job
- order by deptno
- union all
- select null deptno, null job, sum(sal) from scott.emp;
- select deptno, job, sum(sal)
- from scott.emp
- group by deptno, job
- union all
- select deptno, null job, sum(sal)
- from scott.emp
- group by deptno
- union all
- select null deptno, null job, sum(sal) from scott.emp;
- select deptno, job, sum(sal) from scott.emp group by rollup(deptno, job);
- select deptno, job, sum(sal)
- from scott.emp
- group by deptno, job
- union all
- select deptno, null job, sum(sal)
- from scott.emp
- group by deptno
- union all
- select null deptno, job, sum(sal)
- from scott.emp
- group by job
- union all
- select null deptno, null job, sum(sal) from scott.emp;
- select grouping(job),deptno, job, sum(sal)
- from scott.emp
- group by cube(deptno, job)
- order by deptno;
- select null deptno, job, sum(sal)
- from scott.emp
- group by job
- union all
- select deptno, null job, sum(sal) from scott.emp group by deptno;
- select grouping(deptno),grouping(job),grouping_id(job), deptno, job, sum(sal)
- from scott.emp
- group by grouping sets(deptno, job);
忙活半天总算把客户的需求都满足了,稍微松了一口气,不过既然学到新东西,我们有必要最后总结一下。
GROUP BY ROLLUP(A,B,C):首先对(A,B,C)进行GROUP BY,然后对(A,B)进行GROUP BY,然后是(A)进行GROUP BY, 最后对全表进行GROUP BY操作。
GROUP BY CUBE(A,B,C):首先对(A,B,C)进行GROUP BY,然后依次对(A,B)、(A,C)、(A)、(B,C)、(B)、(C)进行GROUP BY,最后对全表进行GROUP BY操作。
GROUP BY GROUPING SETS(A,B,C):依次对(C)、(B)、(A)进行GROUP BY。
0 0
- Oracle高级查询,GROUP BY
- Oracle高级查询之GROUP BY
- Oracle高级查询之GROUP BY
- Oracle高级查询之GROUP BY
- Oracle高级查询之GROUP BY
- Oracle高级查询之GROUP BY
- Oracle高级查询之GROUP BY
- 傅老师课堂:Oracle高级查询之GROUP BY
- 傅老师课堂:Oracle高级查询之GROUP BY
- Oracle group by高级用法之rollup
- 笔记:Oracle SQL 高级查询简介 (1) case、层次化、扩展group by
- 59.Oracle数据库SQL开发之 高级查询——使用扩展的GROUP BY子句
- Hive高级查询(group by、 order by、 join等)
- 07-Hive高级查询order by、group by
- Hive高级查询(group by、 order by、 join等)
- Hive高级查询(group by、 order by、 join等)
- Hive高级查询(group by、 order by、 join等)
- Hive高级查询(group by、 order by、 join等)
- ListView 适配器实现getviewtypecount() 数组越界IndexOutOfBoundException
- leetcode笔记:Pow(x, n)
- maven打tar.gz zip包—maven-assembly-plugin
- [知其然不知其所以然-15] cgroup概述
- MyBatis项目-shop购物系统
- Oracle高级查询,GROUP BY
- Xcode7中 http请求报错App Transport Security has blocked a cleartext HTTP
- 虚拟机中使用linux系启用文件共享之后的文件存在的位置
- 2015年年尾总结
- Oracle表分区
- OpenGL 视图变换和投影变换
- xib总结
- 我的拖延症 2015年总结
- 图解Linux命令之--lsmod命令