来源:Oracle认证 发布时间:2012-11-12 Oracle认证视频 评论
可以使用GROUPING_ID函数借助HAVING子句对记录进行过滤,将不包含小计或者总计的记录除去。GROUPING_ID()函数可以接受一列或多列,返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是将按照顺序对每一列调用GROUPING函数的结果组合起来。
1、GROUPING_ID用法实例
SQL> select2 division_id,job_id,3 grouping(division_id) as div_grp,4 grouping(job_id) as job_grp,5 grouping_id(division_id,job_id) as grp_id,6 sum(salary)7 from employees28 group by cube(division_id,job_id)9 order by division_id,job_id;DIV JOB DIV_GRP JOB_GRP GRP_ID SUM(SALARY)--- --- ---------- ---------- ---------- -----------BUS MGR 0 0 0 530000BUS PRE 0 0 0 800000BUS WOR 0 0 0 280000BUS 0 1 1 1610000OPE ENG 0 0 0 245000OPE MGR 0 0 0 805000OPE WOR 0 0 0 270000OPE 0 1 1 1320000SAL MGR 0 0 0 4446000SAL WOR 0 0 0 490000SAL 0 1 1 4936000DIV JOB DIV_GRP JOB_GRP GRP_ID SUM(SALARY)--- --- ---------- ---------- ---------- -----------SUP MGR 0 0 0 465000SUP TEC 0 0 0 115000SUP WOR 0 0 0 435000SUP 0 1 1 1015000ENG 1 0 2 245000MGR 1 0 2 6246000PRE 1 0 2 800000TEC 1 0 2 115000WOR 1 0 2 14750001 1 3 888100021 rows selected.
2、GOURPING位向量计算
如上例所示
division_id job_id 位向量 GROUPING_ID()返回值
非空 非空 00 0
非空 空 01 1
空 非空 10 2
空 空 11 3
3、GROUPING_ID()的用武之地
GROUPING_ID()的一个用武之地在于使用HAVING子句过滤记录。HAVING子句可以将不包含小计或总计的记录除去,这只要通过简单的检查GROUPING_ID()的返回值,看其是否大于零就可以实现。
SQL> select2 division_id,job_id,3 grouping_id(division_id,job_id) as grp_id,4 sum(salary)5 from employees26 group by cube(division_id,job_id)7 having grouping_id(division_id,job_id) > 08 order by division_id,job_id;DIV JOB GRP_ID SUM(SALARY)--- --- ---------- -----------BUS 1 1610000OPE 1 1320000SAL 1 4936000SUP 1 1015000ENG 2 245000MGR 2 6246000PRE 2 800000TEC 2 115000WOR 2 14750003 888100010 rows selected.
4、在GROUP BY子句中多次使用一个列
在GROUP BY子句中可以多次使用某个列,这样可以实现对数据的重新组织,或是按照不同的数据分组进行统计。例如,下面这个查询中包含一个GROUP BY子句,其中使用了两次division_id列,第一次是对division_id进行分组,第二次是在ROLLUP中使用。
SQL> select division_id,job_id,sum(salary) 2 from employees2 3 group by division_id,rollup(division_id,job_id); DIV JOB SUM(SALARY) --- --- ----------- BUS MGR 530000 BUS PRE 800000 BUS WOR 280000 OPE ENG 245000 OPE MGR 805000 OPE WOR 270000 SAL MGR 4446000 SAL WOR 490000 SUP MGR 465000 SUP TEC 115000 SUP WOR 435000 DIV JOB SUM(SALARY) --- --- ----------- BUS 1610000 OPE 1320000 SAL 4936000 SUP 1015000 BUS 1610000 OPE 1320000 SAL 4936000 SUP 1015000 19 rows selected.
但是需要注意,最后四行记录和前面四行记录是重复的。这种重复现象可以通过使用GROUP_ID()来消除。
5、使用GROUP_ID函数
GROUP_ID函数可用于消除GROUP BY子句返回的重复记录。GROUP_ID()不接受任何参数。如果某个特定的分组重复出现n次,那么GROUP_ID()返回从0到n-1之间的一个整数。下面我们重写上面那个例子
SQL> select division_id,job_id,group_id(),sum(salary) 2 from employees2 3 group by division_id,rollup(division_id,job_id); DIV JOB GROUP_ID() SUM(SALARY) --- --- ---------- ----------- BUS MGR 0 530000 BUS PRE 0 800000 BUS WOR 0 280000 OPE ENG 0 245000 OPE MGR 0 805000 OPE WOR 0 270000 SAL MGR 0 4446000 SAL WOR 0 490000 SUP MGR 0 465000 SUP TEC 0 115000 SUP WOR 0 435000 DIV JOB GROUP_ID() SUM(SALARY) --- --- ---------- ----------- BUS 0 1610000 OPE 0 1320000 SAL 0 4936000 SUP 0 1015000 BUS 1 1610000 OPE 1 1320000 SAL 1 4936000 SUP 1 1015000 19 rows selected.
可以通过HAVING子句来消除重复记录,只返回GROUP_ID()等于0的记录。
SQL> select division_id,job_id,group_id(),sum(salary) 2 from employees2 3 group by division_id,rollup(division_id,job_id) 4 having group_id()=0; DIV JOB GROUP_ID() SUM(SALARY) --- --- ---------- ----------- BUS MGR 0 530000 BUS PRE 0 800000 BUS WOR 0 280000 OPE ENG 0 245000 OPE MGR 0 805000 OPE WOR 0 270000 SAL MGR 0 4446000 SAL WOR 0 490000 SUP MGR 0 465000 SUP TEC 0 115000 SUP WOR 0 435000 DIV JOB GROUP_ID() SUM(SALARY) --- --- ---------- ----------- BUS 0 1610000 OPE 0 1320000 SAL 0 4936000 SUP 0 1015000 15 rows selected.
上一篇: Oracle 10g RAC下如何删除listener
下一篇: 在Solaris10下安装oracle
① 凡本网注明稿件来源为"原创"的所有文字、图片和音视频稿件,版权均属本网所有。任何媒体、网站或个人转载、链接转贴或以其他方式复制发表时必须注明"稿件来源:我考网",违者本网将依法追究责任;
② 本网部分稿件来源于网络,任何单位或个人认为我考网发布的内容可能涉嫌侵犯其合法权益,应该及时向我考网书面反馈,并提供身份证明、权属证明及详细侵权情况证明,我考网在收到上述法律文件后,将会尽快移除被控侵权内容。