1rollup字句
rollup字句扩展group by字句,为每一个分组返回一条小计记录,并为全部分组返回总计
列如:rollup字句获取每个部门的工资总计
select d.dname,sum(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by rollup(d.dname)
order by d.dname;
解释:
dname:部门名称
与没有使用rollup比较
下面是rollup多个列分组求总计
select d.dname,e.job,sum(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by rollup(d.dname,e.job)
2cube字句
cube字句对group by进行扩展,返回cube中所有列组合的小计信息,同时在最后显示总计信息
select d.dname,e.job,sum(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by cube(d.dname,e.job)
order by d.dname,e.job
3grouping()函数
grouping()函数可以接受一列,返回0,或者1,
如果列值为空,那么grouping()函数返回1,否则返回0。grouping()函数只能在使用rollup或者cube的查询中使用
下面的查询使用grouping()函数确定列值是否为空
select grouping(d.dname),d.dname,sum(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by rollup(d.dname)
order by d.dname
上面代码进阶
select
case grouping(d.dname)
when 1 then ‘部门工资合计’
else d.dname
end
as deptName,sum(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by rollup(d.dname)
order by d.dname;