grouping Function

缺乏、安全感 2022-09-17 12:12 16阅读 0赞

转自：

http://blog.csdn.net/xiadingling/article/details/7876120

GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。

关于ROLLUP和CUBE函数的使用，请参见我的另一篇文章。

[http://blog.csdn.net/wh62592855/archive/2009/11/16/4817920.aspx][http_blog.csdn.net_wh62592855_archive_2009_11_16_4817920.aspx]

**1、在ROLLUP中对单列使用GROUPING()**

SQL> select division\_id,sum(salary)  
  2  from employees2  
  3  group by rollup(division\_id)  
  4  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)  
  2  from employees2  
  3  group by rollup(division\_id)  
  4  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。

**2、使用CASE转换GROUPING()的返回值**

可能你会觉得前面的0和1太枯燥了，代表不了任何意义，说白了就是不够人性化，呵呵。这个时候我们可以使用CASE来转换为一些有意义的值。

SQL> select  
  2  case grouping(division\_id)  
  3  when 1 then 'all divisions'  
  4  else division\_id  
  5  end as div,  
  6  sum(salary)  
  7  from employees2  
  8  group by rollup(division\_id)  
  9  order by division\_id;

DIV           SUM(SALARY)  
\------------- -----------  
BUS               1610000  
OPE               1320000  
SAL               4936000  
SUP               1015000  
all divisions     8881000

**3、使用CASE和GROUPING()转换多个列的值**

SQL> select  
  2  case grouping(division\_id)  
  3  when 1 then 'all divisions'  
  4  else division\_id  
  5  end as div,  
  6  case grouping(job\_id)  
  7  when 1 then 'all jobs'  
  8  else job\_id  
  9  end as job,  
 10  sum(salary)  
 11  from employees2  
 12  group by rollup(division\_id,job\_id)  
 13  order by division\_id,job\_id;

DIV           JOB      SUM(SALARY)  
\------------- -------- -----------  
BUS           MGR           530000  
BUS           PRE           800000  
BUS           WOR           280000  
BUS           all jobs     1610000  
OPE           ENG           245000  
OPE           MGR           805000  
OPE           WOR           270000  
OPE           all jobs     1320000  
SAL           MGR          4446000  
SAL           WOR           490000  
SAL           all jobs     4936000

DIV           JOB      SUM(SALARY)  
\------------- -------- -----------  
SUP           MGR           465000  
SUP           TEC           115000  
SUP           WOR           435000  
SUP           all jobs     1015000  
all divisions all jobs     8881000

16 rows selected.

**4、CUBE与GROUPING()结合使用**

SQL> select  
  2  case grouping(division\_id)  
  3  when 1 then 'all divisions'  
  4  else division\_id  
  5  end as div,  
  6  case grouping(job\_id)  
  7  when 1 then 'all jobs'  
  8  else job\_id  
  9  end as job,  
 10  sum(salary)  
 11  from employees2  
 12  group by cube(division\_id,job\_id)  
 13  order by division\_id,job\_id;

DIV           JOB      SUM(SALARY)  
\------------- -------- -----------  
SUP           MGR           465000  
SUP           TEC           115000  
SUP           WOR           435000  
SUP           all jobs     1015000  
all divisions ENG           245000  
all divisions MGR          6246000  
all divisions PRE           800000  
all divisions TEC           115000  
all divisions WOR          1475000  
all divisions all jobs     8881000

21 rows selected.

**5、使用GROUPING SETS子句**

使用GROUPING SETS子句可以只返回小计记录。

SQL> select division\_id,job\_id,sum(salary)  
  2  from employees2  
  3  group by grouping sets(division\_id,job\_id)  
  4  order by division\_id,job\_id;

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

9 rows selected.

[http_blog.csdn.net_wh62592855_archive_2009_11_16_4817920.aspx]: http://blog.csdn.net/wh62592855/archive/2009/11/16/4817920.aspx