SQL之17深入group by

ROLLUP等在GROUP BY中的使用[@more@]

ROLLUP在GROUP BY中的使用

SELECT department_id, job_id,SUM(salary)

FROM employees

WHERE department_id < 60

GROUP BY ROLLUP(department_id, job_id);

DEPTNO JOB AVG(SAL)

------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 2916.66667

20 CLERK 950

20 ANALYST 3000

20 MANAGER 2975

20 1968.75

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 1400

30 1566.66667

40

40

50

50 ANALYST 3000

50 3000

2073.21429

結果：除了分組外，還加上按DEPTNO的小計

cube在GROUP BY中的使用

SQL> /

DEPTNO JOB SUM(SAL)

---------- --------- ----------

29025

CLERK 4150

ANALYST 6000

MANAGER 8275

SALESMAN 5600

PRESIDENT 5000

10 8750

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 7875

20 CLERK 1900

20 ANALYST 3000

20 MANAGER 2975

30 9400

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

40

40

50

50 3000

50 ANALYST 3000

24 rows selected.

SQL> ;

1 select deptno,job,sum(sal)

2* from emp group by cube(deptno,job)

SQL>

結果：除了分組外，按DEPTNO的小計，並按job進行sum(sal),顯示在行集前面.

Grouping 結合cube或rollup在GROUP BY中的使用

SQL> ;

1 select deptno,job,sum(sal),

2 grouping(deptno) grp_dept,grouping(job) grp_job

3* from emp where deptno<60 group by cube(deptno,job)

SQL> /

DEPTNO JOB SUM(SAL) GRP_DEPT GRP_JOB

---------- --------- ---------- ---------- ----------

1 0

29025 1 1

CLERK 4150 1 0

ANALYST 6000 1 0

MANAGER 8275 1 0

SALESMAN 5600 1 0

PRESIDENT 5000 1 0

10 8750 0 1

10 CLERK 1300 0 0

10 MANAGER 2450 0 0

10 PRESIDENT 5000 0 0

20 7875 0 1

20 CLERK 1900 0 0

20 ANALYST 3000 0 0

20 MANAGER 2975 0 0

30 9400 0 1

30 CLERK 950 0 0

30 MANAGER 2850 0 0

30 SALESMAN 5600 0 0

40 0 0

40 0 1

50 0 0

50 3000 0 1

50 ANALYST 3000 0 0

24 rows selected.

Grouping sets 代替cube或rollup在GROUP BY中的使用

SQL> select deptno,job,mgr,sum(sal)

2 from emp group by grouping sets

3 ((deptno,job),(job,mgr));

DEPTNO JOB MGR SUM(SAL)

---------- --------- ---------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 CLERK 1900

20 ANALYST 3000

20 MANAGER 2975

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

40

50

50 ANALYST 3000

CLERK 7698 950

CLERK 7782 1300

CLERK 7788 1100

CLERK 7902 800

ANALYST 7566 6000

MANAGER 7839 8275

SALESMAN 7698 5600

PRESIDENT 5000

21 rows selected.