grouping and grouping_id

最新推荐文章于 2022-06-23 09:09:31 发布

ckrsktoni24621798

最新推荐文章于 2022-06-23 09:09:31 发布

阅读量97

点赞数

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
------------------------------------------------