GROUPING 、GROUPING SETS用法

最新推荐文章于 2024-07-17 12:00:00 发布

羊小二

最新推荐文章于 2024-07-17 12:00:00 发布

阅读量2.2k

点赞数

分类专栏： oracle

oracle 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

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