（2.4）DDL增强功能-数据汇总grouping、rollup、cube

参考：https://www.cnblogs.com/nikyxxx/archive/2012/11/27/2791001.html

1.rollup

　　（1）rollup在group by 子句中使用with指定，用于生产包含小计和总计的报表（其实和excel中的分类汇总差不多）

　　

with test as 
(
    select item = 'table' ,color = 'red' ,quantity = 100
    union all
    select item = 'table' ,color = 'greed' ,quantity = 200
    union all
    select item = 'chair' ,color = 'red' ,quantity = 300
    union all
    select item = 'chair' ,color = 'blue' ,quantity = 400
)

select item,color,sum(quantity) num from test
group by item,color with rollup

　　分析：color为null的都是item汇总，item为null的是所有汇总

　　这样不太直观，改一下

with test as 
(
    select item = 'table' ,color = 'red' ,quantity = 100
    union all
    select item = 'table' ,color = 'greed' ,quantity = 200
    union all
    select item = 'chair' ,color = 'red' ,quantity = 300
    union all
    select item = 'chair' ,color = 'blue' ,quantity = 400
)

select item,color,sum(quantity) num 
 ,grouping(item) as 'item_flag',
 grouping(color) as 'color_flag'
from test
group by item,color with rollup    

　我们发现，等于1的就是汇总于是我们可以这样得到我们想要的样子

with test as 
(
    select item = 'table' ,color = 'red' ,quantity = 100
    union all
    select item = 'table' ,color = 'greed' ,quantity = 200
    union all
    select item = 'chair' ,color = 'red' ,quantity = 300
    union all
    select item = 'chair' ,color = 'blue' ,quantity = 400
)

select case when grouping(item)=0 then item else '物品总计' end item,
case when grouping(color)=0 then color else '颜色小计' end color
,sum(quantity) num 
from test
group by item,color with rollup    

2.CUBE

　　和ROLLUP是一样的，这里就不再深究了。

 

 

3.GROUPING

　　是一个聚合函数,它产生一个附加的列，当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0。

仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相联系的选择列表中才允许分组。

语法

　　GROUPING ( column_name )

参数

　　column_name

　　是 GROUP BY 子句中用于检查 CUBE 或 ROLLUP 空值的列。

返回类型

　　int

注释

　　分组用于区分由 CUBE 和 ROLLUP 返回的空值和标准的空值。作为CUBE 或 ROLLUP 操作结果返回的 NULL 是 NULL 的特殊应用。它在结果集内作为列的占位符，意思是"全体"。

示例

　　下面的示例将 royalty 的数值分组，并聚合 advance 的数值。GROUPING 函数应用于 royalty 列。

USE pubs

SELECT royalty, SUM(advance) 'total advance',
GROUPING(royalty) 'grp'
FROM titles
GROUP BY royalty WITH ROLLUP

 

 

结果集在 royalty 下显示两个空值。

　　第一个 NULL 代表从表中这一列得到的空值组。

　　第二个 NULL 在 ROLLUP 操作所添加的汇总行中。

　　汇总行显示的是所有 royalty 组的 advance 合计数值，并且在 grp 列中用 1 标识。

下面是结果集：

royalty        total advance              grp
---------      ---------------------    ---
NULL           NULL                     0 
10             57000.0000               0 
12             2275.0000                0 
14             4000.0000                0 
16             7000.0000                0 
24             25125.0000               0 
NULL           95400.0000               1