现在的位置: 首页 > 数据库 > 正文

Oracle分组子句group by相关子句函数介绍

2014年11月30日 数据库 ⁄ 共 1797字 暂无评论 ⁄ 阅读 1,331 次
文章目录

ROLLUP

group by后使用rollup可进行分组小计,如:

select job,avg(sal)

from emp

group by rollup(job);

会得到以job为分组的平均工资,最后还会得到所有人的平均工资。rollup也可对多列进行分组小计,相当于在所有列基础上每次减少最后一个列然后进行合计,如:

select job,deptno,avg(sal)

from emp

group by rollup(job,deptno);

该语句会先计算每种工作在每个部门的平均工资,然后得到每种工作的平均工资,依次计算,最后得到所有人的平均工资。需要注意的是在rollup中列的顺序将影响到分组小计的计算,在该语句中,以job+deptno为一个组,以job为一个组,以所有数据为一个组。如果将job和deptno顺序对调,则得到以job+deptno为一个组,以depton为一个组,以所有数据为一个组。

CUBE

cube可以做交叉报表,即对列的所有组合形式都求小计,如:

select job,deptno,avg(sal)

from emp

group by cube(job,deptno);

该语句将不仅求每种工作在每个部门的平均工资及每种工作的平均工资,还求出每个部门的平均工资及最终所有人的平均工资,即以job+deptno为一个组,以job为一个组,以deptno为一个组,以所有数据为一个组。对调cube中字段的顺序不会影响结果集,但对结果集的排序有一定影响,Oracle数据库会先得到以所有数据为组的合计,再得到以deptno为小组进行合计,再得到以job为小组的合计,其后为该job+deptno的合计,依次遍历完所有job。

GROUPING SETS子句

在group by后使用grouping sets子句可以只返回小计记录,在求多列分组小计时,使用grouping sets要比rollup和cube效率高。如求出每个部门和平均工资及每种工作的平均工资:

select job,deptno,avg(sal)

from emp

group by grouping sets(job,deptno);

GROUPING()函数

grouping()函数只能在使用rollup或cube的查询中使用,主要用于判断列值是否为空(为空时表示是合计记录,不为空则不为合计记录)。在select子句后使用,如:

select grouping(job),job,avg(sal)

from emp

group by rollup(job);

需要注意的是,grouping()函数只能接受一个列!判断列值是否为空,为空则函数返回1,否则返回0。即,若是分组合计行则返回1,不是合计行返回0。当需要在返回空值的地方显示某个值时,grouping()函数非常有用,如将JOB合计处值设为Total:

select decode(grouping(job),1,'Total',job) job,

avg(sal)

from emp

group by rollup(job);

ROLLUP多列时GROUPING

select decode(grouping(job),1,'ALL',job),

decode(grouping(deptno),1,'Total',deptno),

avg(sal)

from emp

group by rollup(job,deptno);

CUBE多列时GROUPING

select decode(grouping(job),1,'All jobs',job),

decode(grouping(deptno),1,'All dept',deptno),

avg(sal)

from emp

group by cube(job,deptno);

GROUPING_ID()函数

使用grouping()函数时,只能用于判断单列是否为合计行。而grouping_id()函数则可接受一列或多列,返回按位返回向量。如grouping_id(job,deptno):

  • 若两列都非空,则位向量为00,函数返回值0;
  • 若job为非空,deptno为空,则位向量为01,函数返回值1;
  • 若job为空,deptno为非空,则位向量为10,函数返回值2;
  • 若job为空,deptno为空,则位向量为11,函数返回值3;

grouping_id()函数可用于having子句,过滤指定记录,如>0则不要总计行等。

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?