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

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> Grouping(c.fruit_name) + Grouping(c.fruit_type) = <span class="hljs-number">2</span> <span class="hljs-keyword">THEN</span>   <span class="hljs-string">'合计'</span>
            <span class="hljs-keyword">WHEN</span> Grouping(c.fruit_name) = <span class="hljs-number">1</span> <span class="hljs-keyword">THEN</span>    <span class="hljs-string">'小计'</span>
            <span class="hljs-keyword">ELSE</span> c.fruit_name
            <span class="hljs-keyword">END</span> fruit_name,
        <span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> Grouping(c.fruit_name) + Grouping(c.fruit_type) = <span class="hljs-number">2</span> <span class="hljs-keyword">THEN</span> <span class="hljs-string">'合计'</span>
             <span class="hljs-keyword">WHEN</span> Grouping(c.fruit_type) = <span class="hljs-number">1</span> <span class="hljs-keyword">THEN</span> <span class="hljs-string">'小计'</span>
             <span class="hljs-keyword">ELSE</span> c.fruit_type
             <span class="hljs-keyword">END</span> fruit_type,
<span class="hljs-keyword">FROM</span> Al_fruit c
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> rollup (c.fruit_name, c.fruit_type)</span>

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

SQL执行结果为：

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

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

SELECT
    C<span class="hljs-preprocessor">.fruit</span>_name,C<span class="hljs-preprocessor">.fruit</span>_type,c<span class="hljs-preprocessor">.fruit</span>_price,COUNT (c<span class="hljs-preprocessor">.fruit</span>_name) Total
FROM    Al_fruit c
GROUP BY
    rollup (c<span class="hljs-preprocessor">.fruit</span>_name,c<span class="hljs-preprocessor">.fruit</span>_type,c<span class="hljs-preprocessor">.fruit</span>_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

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> Grouping(c.fruit_name) + Grouping(c.fruit_type) = <span class="hljs-number">2</span> <span class="hljs-keyword">THEN</span>   <span class="hljs-string">'合计'</span>
            <span class="hljs-keyword">WHEN</span> Grouping(c.fruit_name) = <span class="hljs-number">1</span> <span class="hljs-keyword">THEN</span>    <span class="hljs-string">'小计'</span>
            <span class="hljs-keyword">ELSE</span> c.fruit_name
            <span class="hljs-keyword">END</span> fruit_name,
        <span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> Grouping(c.fruit_name) + Grouping(c.fruit_type) = <span class="hljs-number">2</span> <span class="hljs-keyword">THEN</span> <span class="hljs-string">'合计'</span>
             <span class="hljs-keyword">WHEN</span> Grouping(c.fruit_type) = <span class="hljs-number">1</span> <span class="hljs-keyword">THEN</span> <span class="hljs-string">'小计'</span>
             <span class="hljs-keyword">ELSE</span> c.fruit_type
             <span class="hljs-keyword">END</span> fruit_type,
<span class="hljs-keyword">FROM</span> Al_fruit c
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> cube ( c.fruit_type,c.fruit_name)</span>

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

执行结果为：

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

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

SELECT
    C<span class="hljs-preprocessor">.fruit</span>_name,C<span class="hljs-preprocessor">.fruit</span>_type,c<span class="hljs-preprocessor">.fruit</span>_price,COUNT (c<span class="hljs-preprocessor">.fruit</span>_name) Total
FROM    Al_fruit c
GROUP BY
    cube(c<span class="hljs-preprocessor">.fruit</span>_name,c<span class="hljs-preprocessor">.fruit</span>_type,c<span class="hljs-preprocessor">.fruit</span>_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