GROUPING 、GROUPING SETS用法_grouping 和grouping sets

GROUPING是一个聚合函数，用在含有CUBE 或 ROLLUP 语句的SQL语句中，当结果集中的数据行是由CUBE 或 ROLLUP 运算产生的（添加的）则该函数返回1，否则返回0。

语法： GROUPING ( column_name )   

其中 column_name 是用在CUBE 或 ROLLUP 运算的列 或group by 后的列。

注意：

（1）只有使用了CUBE 或 ROLLUP 运算符的SQL中才能使用GROUPING

（2）GROUPING 后面的列 名可以是CUBE 或 ROLLUP 运算符中使用的列名，也可以是group by 中的列名

举例说明

创建表：

CREATE TABLE DEPART (部门 char(10),员工 char(6),工资 int)

INSERT INTO DEPART SELECT 'A','ZHANG',100   
INSERT INTO DEPART SELECT 'A','LI',200    
INSERT INTO DEPART SELECT 'A','WANG',300    
INSERT INTO DEPART SELECT 'A','ZHAO',400    
INSERT INTO DEPART SELECT 'A','DUAN',500    
INSERT INTO DEPART SELECT 'B','DUAN',600    
INSERT INTO DEPART SELECT 'B','DUAN',700

表中数据：

部门         员工         工资

A             ZHANG     100   
A             LI             200    
A             WANG      300    
A             ZHAO      400    
A             DUAN      500    
B             DUAN      600    
B             DUAN      700

（1）GROUPING的作用

A:先执行一个ROLLUP，看看结果 以便对比

SELECT 部门,员工,SUM(工资)AS TOTAL   
FROM DEPART    
GROUP BY  部门,员工  WITH ROLLUP

结果：

部门         员工        TOTAL

A             DUAN       500   
A             LI             200    
A             WANG      300    
A             ZHANG     100    
A             ZHAO       400    
A             NULL       1500    
B             DUAN      1300    
B             NULL       1300    
NULL      NULL        2800

B:在A  的基础上 加上GROUPING ，执行下面的SQL(GROUPING中的列名是ROLLUP的列名)

SELECT 部门,员工,SUM(工资)AS TOTAL,GROUPING(员工) AS 'Grouping'   
FROM DEPART    
GROUP BY  部门,员工  WITH ROLLUP

结果：

部门         员工        TOTAL  Grouping

A             DUAN      500         0   
A             LI             200        0    
A             WANG      300        0    
A             ZHANG     100         0    
A             ZHAO      400          0    
A             NULL        1500       1    
B             DUAN      1300        0    
B             NULL       1300       1    
NULL       NULL       2800       1

C: 在A 的基础上 加上GROUPING ，执行下面的SQL(GROUPING中的列名是GROUP BY后的列名，但不是ROLLUP的列名)

SELECT 部门,员工,SUM(工资)AS TOTAL,GROUPING(部门) AS 'Grouping'   
FROM DEPART    
GROUP BY  部门,员工  WITH ROLLUP

结果：

部门         员工        TOTAL  Grouping

A             DUAN      500         0   
A             LI             200        0    
A             WANG      300        0    
A             ZHANG     100        0    
A             ZHAO      400         0    
A             NULL       1500       0    
B             DUAN      1300       0    
B             NULL     1300        0    
NULL       NULL     2800        1

看到了没？GROUPING就是用来测试结果集中的那些数据是CUBE 或 ROLLUP添加进去的，是则 GROUPIN返回1不是则返回0。这样一来他的用处就出来啦。

（2）GROUPING用法

可以用在HAVING语句中，用去选取或去掉合计值，对比上面的执行结果看看下面的执行结果 ，你就什么都明白了。

SELECT 部门,员工,SUM(工资)AS TOTAL   
FROM DEPART    
GROUP BY  部门,员工  WITH ROLLUP    
HAVING GROUPING(员工)=1

结果：

部门         员工        TOTAL

A             NULL    1500   
B             NULL    1300    
NULL        NULL    2800

SELECT 部门,员工,SUM(工资)AS TOTAL   
FROM DEPART    
GROUP BY  部门,员工  WITH ROLLUP    
HAVING GROUPING(员工)=0

结果：

部门         员工        TOTAL

A             DUAN      500   
A             LI             200    
A             WANG      300    
A             ZHANG     100    
A             ZHAO      400    
B             DUAN      1300

SELECT 部门,员工,SUM(工资)AS TOTAL   
FROM DEPART    
GROUP BY  部门,员工  WITH ROLLUP    
HAVING GROUPING(部门) =1

结果：

部门         员工        TOTAL

NULL      NULL         2800

 

 

grouping sets可以合并多个分组的结果

SQL> select deptno,avg(sal) from emp group by deptno;

    DEPTNO   AVG(SAL)
---------- ----------
        10 2916.66667
        20       2175
        30 1566.66667

SQL> select job,avg(sal) from emp group by job;

JOB         AVG(SAL)
--------- ----------
ANALYST         3000
CLERK         1037.5
MANAGER   2758.33333
PRESIDENT       5000
SALESMAN        1400

SQL> select deptno,job,avg(sal) from emp group by grouping sets( deptno,job);

    DEPTNO JOB         AVG(SAL)
---------- --------- ----------
           ANALYST         3000
           CLERK         1037.5
           MANAGER   2758.33333
           PRESIDENT       5000
           SALESMAN        1400
        10           2916.66667
        20                 2175
        30           1566.66667