欢迎光临
个人知识库,ERP、IT知识分享和应用

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

未经允许不得转载:Blog.XiaoMing.Xyz » SQL 中 group By rollup 与 group By cube,grouping函数使用说明
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址