ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 047-088

047-088

原创 Linux操作系统 作者:jbymy2000 时间:2012-03-18 10:22:54 0 删除 编辑
88. View the Exhibit1 and examine the descriptions of the EMPLOYEES and
DEPARTMENTS tables.The following SQL statement was executed:
SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total,
GROUPING(e.department_id) GRP_DEPT,GROUPING(e.job_id) GRP_JOB,
GROUPING(d.location_id) GRP_LOC FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY ROLLUP (e.department_id, e.job_id, d.location_id);
View the Exhibit2 and examine the output of the command.
Which two statements are true regarding the output? (Choose two.)
A. The value 1 in GRP_LOC means that the LOCATION_ID column is taken into
account to generate the subtotal.
B. The value 1 in GRP_JOB and GRP_LOC means that JOB_ID and LOCATION_ID
columns are not taken into account to generate the subtotal.
C. The value 1 in GRP_JOB and GRP_LOC means that the NULL value in JOB_ID
and LOCATION_ID columns are taken into account to generate the subtotal.
D. The value 0 in GRP_DEPT, GRP_JOB, and GRP_LOC means that DEPARTMENT_ID,
JOB_ID, and LOCATION_ID columns are taken into account to generate the
subtotal.
Answer: BD
GROUPING函数可以接受一列,返回0或者1。GROUPING只能在使用ROLLUP或CUBE的查询中使用。
当需要在返回空值的地方显示某个值时,GROUPING()就非常有用。
1、在ROLLUP中对单列使用GROUPING()
SQL> select division_id,sum(salary)
from employees2
group by rollup(division_id)
order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
加上GROUPING来看看
SQL> select grouping(division_id),division_id,sum(salary)
from employees2
group by rollup(division_id)
order by division_id;
GROUPING(DIVISION_ID) DIV SUM(SALARY)
--------------------- --- -----------
0 BUS 1610000
0 OPE 1320000
0 SAL 4936000
0 SUP 1015000
1 8881000
可以看到,为空的地方返回1,非空的地方返回0。
什么地方用:rollup 和cube带来的一个问题是,在返会的结果中如何能准确区分出那些是小
计,哪些是汇总数据呢。这点可以使用grouping和grouping_id函数解决。
容易被C选项误导,如果该列没有参加计算,则为1,并不是说该列值为空

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11312660/viewspace-718859/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 047-083
下一篇: 047-093
请登录后发表评论 登录
全部评论

注册时间:2012-01-10

  • 博文量
    416
  • 访问量
    213870