续:
用ROLLUP和CUBE的GROUP BY语句中会出现某些字段的值为空,那么如何来区分哪些空是该字段的值自然为空还是由于ROLLUP和CUBE产生的空呢?这就用到GROUPING
示例:
SQL> select department_id,job_id,sum(salary), 2 grouping(department_id) as "GRP_DEPT",grouping(job_id) as "GRP_JOB" 3 from employees 4 where department_id<50 group by rollup(department_id,job_id);
DEPARTMENT_ID JOB_ID SUM(SALARY) GRP_DEPT GRP_JOB ------------- ---------- ----------- ---------- ---------- 10 AD_ASST 4400 0 0 10 4400 0 1 20 MK_MAN 13000 0 0 20 MK_REP 6000 0 0 20 19000 0 1 30 PU_MAN 11000 0 0 30 PU_CLERK 13900 0 0 30 24900 0 1 40 HR_REP 6500 0 0 40 6500 0 1 54800 1 1
11 rows selected.
分析:1表示因ROLLUP和CUBE产生的空,0表示自然的空。
GROUPING SETS:用小括号把几个子字段组合起来,视为一个字段的情况处理
示例:
SQL> select department_id,job_id,manager_id,avg(salary) from employees group by grouping sets((department_id,job_id),(job_id,manager_id));
DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) ------------- ---------- ---------- ----------- AC_MGR 101 12008 SH_CLERK 122 3200 SH_CLERK 124 2825 MK_MAN 100 13000 ST_MAN 100 7280 ST_CLERK 121 2675 SA_REP 148 8650 SH_CLERK 120 2900 AD_ASST 101 4400 AD_PRES 24000 FI_MGR 101 12008
DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) ------------- ---------- ---------- ----------- SA_REP 146 8500 SH_CLERK 123 3475 IT_PROG 102 9000 IT_PROG 103 4950 FI_ACCOUNT 108 7920 PU_MAN 100 11000 AC_ACCOUNT 205 8300 ST_CLERK 122 2700 SA_REP 145 8500 HR_REP 101 6500 PR_REP 101 10000
DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) ------------- ---------- ---------- ----------- AD_VP 100 17000 ST_CLERK 120 2625 ST_CLERK 124 2925 SA_REP 147 7766.66667 SA_REP 149 8333.33333 ST_CLERK 123 3000 SH_CLERK 121 3675 MK_REP 201 6000 PU_CLERK 114 2780 SA_MAN 100 12200 110 AC_ACCOUNT 8300
DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) ------------- ---------- ---------- ----------- 90 AD_VP 17000 50 ST_CLERK 2785 80 SA_REP 8396.55172 110 AC_MGR 12008 50 ST_MAN 7280 80 SA_MAN 12200 50 SH_CLERK 3215 20 MK_MAN 13000 90 AD_PRES 24000 60 IT_PROG 5760 100 FI_MGR 12008
DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) ------------- ---------- ---------- ----------- 30 PU_CLERK 2780 100 FI_ACCOUNT 7920 70 PR_REP 10000 SA_REP 7000 10 AD_ASST 4400 20 MK_REP 6000 40 HR_REP 6500 30 PU_MAN 11000
52 rows selected.
|