ORACLE GROUPING函数的使用  _MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2047 | 回复: 0   主题: ORACLE GROUPING函数的使用          下一篇 
jun.zhang
注册用户
等级:上士
经验:278
发帖:76
精华:0
注册:2011-12-27
状态:离线
发送短消息息给jun.zhang 加好友    发送短消息息给jun.zhang 发消息
发表于: IP:您无权察看 2015-7-27 18:20:54 | [全部帖] [楼主帖] 楼主

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


--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论