SQL 中 group By rollup 与 group By cube,grouping函数使用说明

https://blog.csdn.net/qq_27190353/article/details/80206723

group By 语句不仅适用于Oracle ，其他数据库也同样适用，本文以SQLserver 数据库为例。

group By 语句除了最基本的语法外，还支持ROLLUP和CUBE语句。CUBE ROLLUP 是用于统计数据的。例如求数据的合计与小计。

grouping 函数使用

1. rollup

假设使用group by rollup(a,b)，首先会对(a,b)进行group by ，然后对 a 进行 group by 。最后对全表进行 group by 操作。

• 1

例如SQL

SELECT CASE WHEN Grouping(c.fruit_name) + Grouping(c.fruit_type) = 2 THEN   '合计'
            WHEN Grouping(c.fruit_name) = 1 THEN    '小计'
            ELSE c.fruit_name
            END fruit_name,
        CASE WHEN Grouping(c.fruit_name) + Grouping(c.fruit_type) = 2 THEN '合计'
             WHEN Grouping(c.fruit_type) = 1 THEN '小计'
             ELSE c.fruit_type
             END fruit_type,
FROM Al_fruit c
GROUP BY rollup (c.fruit_name, c.fruit_type)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

SQL执行结果为：

fruit_type列为水果类型。
可以看到，首先对水果种类和水果类型进行分组，算出每个水果种类和水果类型的数量。然后对水果种类进行一个分组，算出每个水果种类的数量，也就是小计。
最后对全表进行分组，算出所有的水果总数也就是合计。
注：group By rollup 括号中的顺序不能改变。否则则变成，首先对水果种类和水果类型进行分组，算出每个水果种类和水果类型的数量。然后对水果类型进行一个分组，算出每个水果类型的数量，也就是小计。最后对全表进行分组，算出所有的水果总数也就是合计。
对应的执行结果为：

可以看出两者的执行结果条数不相同。
如果是三个字段用group by rollup进行求合计小计。

SELECT
    C.fruit_name,C.fruit_type,c.fruit_price,COUNT (c.fruit_name) Total
FROM    Al_fruit c
GROUP BY
    rollup (c.fruit_name,c.fruit_type,c.fruit_price)

• 1
• 2
• 3
• 4
• 5

执行结果为：

可看出先对三个字段进行分组（fruit_name水果名称,；fruit_type水果种类；fruit_price水果价格），然后对水果名称和水果种类两个字段进行分组，然后对水果名称一个字段进行分组，然后对全表进行分组。同样也要注意需要分组的字段顺序。
读者如果有需要按4个及4个以上字段进行分组的，可以按照这个顺序进行计算。
分组的次数=待分组的字段数+1

2. cube

假设使用group by cube(a,b)，首先会对(a,b)进行group by ，然后对 a 进行 group by ，然后对 b 进行group by ，最后对全表进行 group by 操作。可以看出cube会列出所有可能的分组。
例如SQL

SELECT CASE WHEN Grouping(c.fruit_name) + Grouping(c.fruit_type) = 2 THEN   '合计'
            WHEN Grouping(c.fruit_name) = 1 THEN    '小计'
            ELSE c.fruit_name
            END fruit_name,
        CASE WHEN Grouping(c.fruit_name) + Grouping(c.fruit_type) = 2 THEN '合计'
             WHEN Grouping(c.fruit_type) = 1 THEN '小计'
             ELSE c.fruit_type
             END fruit_type,
FROM Al_fruit c
GROUP BY cube ( c.fruit_type,c.fruit_name)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

执行结果为：

从执行结果中可以看出先按水果名称和水果种类进行分组，然后按水果名称进行分组，然后按水果种类进行分组，然后对全表进行分组。总共2ⁿ-1=2²-1=3次
此时若改变分组的顺序

两者返回的结果集一致，只是顺序不同而已。
同样，如果是三个字段进行分组：

SELECT
    C.fruit_name,C.fruit_type,c.fruit_price,COUNT (c.fruit_name) Total
FROM    Al_fruit c
GROUP BY
    cube(c.fruit_name,c.fruit_type,c.fruit_price)

• 1
• 2
• 3
• 4
• 5

会按照水果名称，水果名称，水果类型，水果价格进行分组，然后（水果名称，水果类型）（联合），水果价格进行分组，然后水果名称，（水果类型，水果价格）（联合），进行分组，然后水果类型，（水果名称，水果价格）（联合），进行分组，最后全表分组。总共2ⁿ-1=2³-1=7次。
执行的SQL结果为：

读者如果有需要按4个及4个以上字段进行分组的，可以按照这个顺序进行计算。
分组的次数=2ⁿ-1；n为待分组的字段个数。

3. grouping

grouping函数

GROUPING 是一个聚合函数,它产生一个附加的列，当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0。
仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相联系的选择列表中才允许分组。
语法
GROUPING ( column_name )
参数
column_name
是 GROUP BY 子句中用于检查 CUBE 或 ROLLUP 空值的列。
返回类型
int