GROUP函数-GROUP_ID,GROUPING,GROUPING_ID

图片 1

转自: http://www.maomao365.com/?p=6208  

GROUP_ID

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

摘要:
GROUPING 用于区分列是否由 ROLLUP、CUBE
或 GROUPING SETS 聚合而产生的行

如果是原生态的行聚合,则返回0
,新增的行数据就返回1

首先我们看看官方的解释:

语法: GROUPING ( column_name )   


图片 1

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

grouping 语法简介 :
GROUPING (<列名>)
参数列名:

返回值
tinyint
<hr />
grouping 应用举例:
 

大意是GROUP_ID用于区分相同分组标准的分组统计结果。

注意:

create table test(info varchar(30))
go
insert into test (info)values('a'),
('b'),('a'),('c'),('d'),('d') 
go

select info,count_big(info),grouping(info)
from test group by info 
WITH ROLLUP

go
drop table test 
----输出----
ifno 无列名 无列名
a    2    0
b    1    0
c    1    0
d    2    0
NULL    6    1

解释起来比较抽象,下面我们来看看具体的案例。

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

 

例1:单一分组

(2)GROUPING 后面的列 名可以是CUBE 或 ROLLUP
运算符中使用的列名,也可以是group by 中的列名

SQL> select group_id(),deptno,sum(sal) from emp group by rollup(deptno);

GROUP_ID()     DEPTNO    SUM(SAL)
---------- ----------  ----------
         0         10        8750
         0         20       10875
         0         30        9400
         0                  29025

 

rollup(deptno)只是一个唯一的分组,所以产生的group_id()为0,代表这是同一个分组的结果。

举例说明

创建表:

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:重复分组

(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

SQL> select group_id(),deptno,sum(sal) from emp group by rollup(deptno,deptno);

GROUP_ID()      DEPTNO    SUM(SAL)
----------  ---------- ----------
         0         10        8750
         0         20       10875
         0         30        9400
         1         10        8750
         1         20       10875
         1         30        9400
         0                  29025

7 rows selected.

group_id()为1代表这些是重复的分组。

注意:可通过having group_id()
<1来剔除重复的分组。

老实说,我也看不出GROUP_ID在实际工作中的应用场景,姑且先记着。

 

GROUPING

其语法为:GROUPING(expr)

下面我们来看看官方的解释:

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图