原文: http://blog.csdn.net/wh62592855/article/details/4818236

?

? ?可以使用GROUPING_ID函數(shù)借助HAVING子句對記錄進行過濾，將不包含小計或者總計的記錄除去。GROUPING_ID()函數(shù)可以接受一列或多列，返回GROUPING位向量的十進制值。GROUPING位向量的計算方法是將按照順序對每一列調用GROUPING函數(shù)的結果組合起來。

?

關于GROUPING函數(shù)的使用方法可以參見我前面寫的一篇文章

http://blog.csdn.net/wh62592855/archive/2009/11/16/4818072.aspx

?

1、GROUPING_ID用法實例

SQL> select
? ? division_id,job_id,
? ? grouping(division_id) as div_grp,
? ? grouping(job_id) as job_grp,
? ? grouping_id(division_id,job_id) as grp_id,
? ? sum(salary)
? ? from employees2
? ? group by cube(division_id,job_id)
? ? 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

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()的返回值，看其是否大于零就可以實現(xiàn)。

SQL> select
? ? division_id,job_id,
? ? grouping_id(division_id,job_id) as grp_id,
? ? sum(salary)
? ? from employees2
? ? group by cube(division_id,job_id)
? ? having grouping_id(division_id,job_id) > 0
? ?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子句中可以多次使用某個列，這樣可以實現(xiàn)對數(shù)據(jù)的重新組織，或是按照不同的數(shù)據(jù)分組進行統(tǒng)計。例如，下面這個查詢中包含一個GROUP BY子句，其中使用了兩次division_id列，第一次是對division_id進行分組，第二次是在ROLLUP中使用。

SQL> select division_id,job_id,sum(salary)
? ? from employees2
? ? 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.

?

但是需要注意，最后四行記錄和前面四行記錄是重復的。這種重復現(xiàn)象可以通過使用GROUP_ID()來消除。

?

5、使用GROUP_ID函數(shù)

GROUP_ID函數(shù)可用于消除GROUP BY子句返回的重復記錄。GROUP_ID()不接受任何參數(shù)。如果某個特定的分組重復出現(xiàn)n次，那么GROUP_ID()返回從0到n-1之間的一個整數(shù)。下面我們重寫上面那個例子

SQL> select division_id,job_id,group_id(),sum(salary)
? ? from employees2
? ? 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)
? ? from employees2
? ? group by division_id,rollup(division_id,job_id)
? ? 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 GROUPING_ID函數(shù)