一.GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。
1、在ROLLUP中对单列使用GROUPING()
select employee_id,sum(salary)
from employees
group by employee_id
order by employee_id;
--part of results：
EMPLOYEE_ID SUM(SALARY)
----------- -----------
100 24000
101 17000
102 17000
103 9000
104 6000
105 4800
106 4800
加上GROUPING来看看：
select grouping(employee_id),employee_id,sum(salary)
from employees
group by rollup(employee_id)
order by employee_id;
--part of results：
0 195 2800
0 196 3100
0 197 3000
0 198 2600
0 199 2600
0 200 4400
0 201 13000
0 202 6000
0 203 6500
0 204 10000
GROUPING(EMPLOYEE_ID) EMPLOYEE_ID SUM(SALARY)
--------------------- ----------- -----------
0 205 12000
0 206 8300
1 695051.47
2、使用CASE转换GROUPING()的返回值
可能你会觉得前面的0和1太枯燥了，代表不了任何意义，说白了就是不够人性化，呵呵。这个时候我们可以使用CASE来转换为一些有意义的值。
select
case grouping(employee_id)
when 1 then 'all divisions'
else to_char(employee_id)
end as div
,employee_id,sum(salary)
from employees
group by rollup(employee_id)
order by employee_id;
-----part of results：
DIV EMPLOYEE_ID SUM(SALARY)
---------------------------------------- ----------- -----------
205 205 12000
206 206 8300
all divisions 695051.47
3、使用CASE和GROUPING()转换多个列的值
select
case grouping(employee_id)
when 1 then 'all divisions'
else to_char(employee_id)
end as div
,
case grouping(job_id)
when 1 then 'all jobs'
else job_id
end as job,
sum(salary)
from employees
group by rollup(employee_id,job_id)
order by employee_id,job_id;
-----part of results：
DIV JOB SUM(SALARY)
---------------------------------------- ---------- -----------
194 all jobs 3200
195 SH_CLERK 2800
195 all jobs 2800
196 SH_CLERK 3100
196 all jobs 3100
197 SH_CLERK 3000
197 all jobs 3000
198 SH_CLERK 2600
198 all jobs 2600
199 SH_CLERK 2600
199 all jobs 2600
200 AD_ASST 4400
200 all jobs 4400
201 MK_MAN 13000
201 all jobs 13000
202 MK_REP 6000
202 all jobs 6000
203 HR_REP 6500
203 all jobs 6500
204 PR_REP 10000
204 all jobs 10000
DIV JOB SUM(SALARY)
---------------------------------------- ---------- -----------
205 AC_MGR 12000
205 all jobs 12000
206 AC_ACCOUNT 8300
206 all jobs 8300
all divisions all jobs 695051.47
215 rows selected.
4、CUBE与GROUPING()结合使用
select
case grouping(employee_id)
when 1 then 'all divisions'
else to_char(employee_id)
end as div
,
case grouping(job_id)
when 1 then 'all jobs'
else job_id
end as job,
sum(salary)
from employees
group by cube(employee_id,job_id)
order by employee_id,job_id;
-----part of results：
DIV JOB SUM(SALARY)
---------------------------------------- ---------- -----------
194 all jobs 3200
195 SH_CLERK 2800
195 all jobs 2800
196 SH_CLERK 3100
196 all jobs 3100
197 SH_CLERK 3000
197 all jobs 3000
198 SH_CLERK 2600
198 all jobs 2600
199 SH_CLERK 2600
199 all jobs 2600
200 AD_ASST 4400
200 all jobs 4400
201 MK_MAN 13000
201 all jobs 13000
202 MK_REP 6000
202 all jobs 6000
203 HR_REP 6500
203 all jobs 6500
204 PR_REP 10000
204 all jobs 10000
DIV JOB SUM(SALARY)
---------------------------------------- ---------- -----------
205 AC_MGR 12000
205 all jobs 12000
206 AC_ACCOUNT 8300
206 all jobs 8300
all divisions AC_ACCOUNT 8300
all divisions AC_MGR 12000
all divisions AD_ASST 4400
all divisions AD_PRES 24000
all divisions AD_VP 34000
all divisions FI_ACCOUNT 39600
all divisions FI_MGR 12000
all divisions HR_REP 6500
all divisions IT_PROG 28800
all divisions MK_MAN 13000
all divisions MK_REP 6000
all divisions PR_REP 10000
all divisions PU_CLERK 13900
all divisions PU_MAN 11000
all divisions SA_MAN 61000
all divisions SA_REP 254151.47
all divisions SH_CLERK 64300
DIV JOB SUM(SALARY)
---------------------------------------- ---------- -----------
all divisions ST_CLERK 55700
all divisions ST_MAN 36400
all divisions all jobs 695051.47
234 rows selected.
5、使用GROUPING SETS子句
使用GROUPING SETS子句可以只返回小计记录：
select employee_id,job_id,sum(salary)
from employees
group by grouping sets(employee_id,job_id)
order by employee_id,job_id;
-----part of results：
EMPLOYEE_ID JOB_ID SUM(SALARY)
----------- ---------- -----------
205 12000
206 8300
AC_ACCOUNT 8300
AC_MGR 12000
AD_ASST 4400
AD_PRES 24000
AD_VP 34000
FI_ACCOUNT 39600
FI_MGR 12000
HR_REP 6500
IT_PROG 28800
MK_MAN 13000
MK_REP 6000
PR_REP 10000
PU_CLERK 13900
PU_MAN 11000
SA_MAN 61000
SA_REP 254151.47
SH_CLERK 64300
ST_CLERK 55700
ST_MAN 36400
126 rows selected.
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-7-27 18:20:54 |
赞(
操作
