Oracle grouping_id group_id grouping介绍(二)
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 www.2cto.com
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.
作者 runming918