ORACLE GROUPING_ID函数

ORACLE GROUPING_ID函数

可以使用GROUPING_ID函数借助HAVING子句对记录进行过滤，将不包含小计或者总计的记录除去。GROUPING_ID()函数可以接受一列或多列，返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是将按照顺序对每一列调用GROUPING函数的结果组合起来。

关于GROUPING函数的使用方法可以参见另一篇文章
http://blog.csdn.net/wh62592855/archive/2009/11/16/4818072.aspx

1、GROUPING_ID用法实例

SQL> select
  2  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 employees2
  8  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          530000
BUS PRE                  0                  0                  0          800000
BUS WOR                  0                  0                  0          280000
BUS                          0                  1                  1        1610000
OPE ENG                  0                  0                  0          245000
OPE MGR                  0                  0                  0          805000
OPE WOR                  0                  0                  0          270000
OPE                          0                  1                  1        1320000
SAL MGR                  0                  0                  0        4446000
SAL WOR                  0                  0                  0          490000
SAL                          0                  1                  1        4936000

DIV JOB      DIV_GRP      JOB_GRP        GRP_ID SUM(SALARY)
--- --- ---------- ---------- ---------- -----------
SUP MGR                  0                  0                  0          465000
SUP TEC                  0                  0                  0          115000
SUP WOR                  0                  0                  0          435000
SUP                          0                  1                  1        1015000
      ENG                  1                  0                  2          245000
      MGR                  1                  0                  2        6246000
      PRE                  1                  0                  2          800000
      TEC                  1                  0                  2          115000
      WOR                  1                  0                  2        1475000
                                1                  1                  3        8881000
21 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> select
  2  division_id,job_id,
  3  grouping_id(division_id,job_id) as grp_id,
  4  sum(salary)
  5  from employees2
  6  group by cube(division_id,job_id)
  7  having grouping_id(division_id,job_id) > 0
  8  order by division_id,job_id;

DIV JOB        GRP_ID SUM(SALARY)
--- --- ---------- -----------
BUS                          1        1610000
OPE                          1        1320000
SAL                          1        4936000
SUP                          1        1015000
      ENG                  2          245000
      MGR                  2        6246000
      PRE                  2          800000
      TEC                  2          115000
      WOR                  2        1475000
                                3        8881000

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

转自：http://blog.csdn.net/wh62592855/article/details/4818236