ITPub博客

首页 > Linux操作系统 > Linux操作系统 > grouping and grouping_id

grouping and grouping_id

原创 Linux操作系统 作者:hjianping 时间:2011-05-04 21:11:40 0 删除 编辑

rollup中使用grouping_id()
select grouping_id(deptno,ename) gid,deptno,ename,sum(sal)
from emp
group by rollup(deptno,ename);


case和grouping()转换单列的值
------------------------------------------------
select case grouping (dname)
   when 1 then 'all dept'
   else dname
  end
   as dname,
     sum(sal) sal
    from emp,dept
where emp.deptno=dept.deptno
group by rollup (dname)
order by dname;
------------------------------------------------
ACCOUNTING 8750
OPERATIONS 6000
RESEARCH 9675
SALES  9400
all dept 33825
------------------------------------------------

case和grouping()转换多个列的值
------------------------------------------------
select case grouping (dname)
   when 1 then 'all dept'
   else dname
  end
   as dname,
  case grouping (ename)
            when 1 then 'all name'
            else ename
         end
            as ename,
     sum(sal) sal
    from emp,dept
where emp.deptno=dept.deptno
group by rollup (dname,ename)
order by dname,ename
------------------------------------------------
ACCOUNTING CLARK  2450
ACCOUNTING KING  5000
ACCOUNTING MILLER  1300
ACCOUNTING all name 8750
OPERATIONS all name 6000
OPERATIONS hjp  6000
RESEARCH ADAMS  1100
RESEARCH FORD  3000
RESEARCH JONES  2975
RESEARCH SCOTT  1800
RESEARCH SMITH  800
RESEARCH all name 9675
SALES  ALLEN  1600
SALES  BLAKE  2850
SALES  JAMES  950
SALES  MARTIN  1250
SALES  TURNER  1500
SALES  WARD  1250
SALES  all name 9400
all dept all name 33825
------------------------------------------------

grouping sets子句
------------------------------------------------
select deptno,empno,sum(sal)
from emp
group by grouping sets(deptno,empno)
order by deptno,empno
------------------------------------------------
10  8750
20  9675
30  9400
40  6000
 7369 800
 7499 1600
 7521 1250
 7566 2975
 7654 1250
 7698 2850
 7782 2450
 7788 1800
 7839 5000
 7844 1500
 7876 1100
 7900 950
 7902 3000
 7934 1300
 8000 6000
------------------------------------------------

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17012874/viewspace-694449/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 管理归档日志
请登录后发表评论 登录
全部评论

注册时间:2011-04-24

  • 博文量
    80
  • 访问量
    72823