Oracle分组函数之ROLLUP、GROUPING用法

小灰灰 2022-12-29 06:40 157阅读 0赞

**一、简要介绍**

1.  rollup用于分组统计，也属于oracle分析函数的一种，同理：grouping也是；
2.  假设有n个维度，那么rollup会有n个聚合，cube会有2n个聚合  
    rollup统计列  
    rollup(a,b) 统计列包含：(a,b)、(a)、()  
    rollup(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a)、()

**二、实践过程**

select a.deptname name, sum(b.sal) total
      from dept a, emp b
     where a.deptno = b.deptno
     group by a.deptname
    union all
    select null,sum(b.sal)
    from dept a, emp b
     where a.deptno = b.deptno;
     等同于：
    select a.deptname, sum(b.sal)
    from dept a, emp b
    where a.deptno = b.deptno
    group by rollup(a.deptname);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sZWlsZWk2NjY_size_16_color_FFFFFF_t_70]

select a.deptname name, b.job, sum(b.sal) total
      from dept a, emp b
     where a.deptno = b.deptno
     group by rollup(a.deptname, b.job)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sZWlsZWk2NjY_size_16_color_FFFFFF_t_70 1]

select case grouping(a.deptname)
              when 1 then
               '总计'
              else
               a.deptname
            end as dname,
            case grouping(b.job)
              when 1 then
               '分计'
              else
               b.job
            end as job,
            sum(b.sal) total
       from dept a, emp b
      where a.deptno = b.deptno
      group by rollup(a.deptname, b.job)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sZWlsZWk2NjY_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sZWlsZWk2NjY_size_16_color_FFFFFF_t_70]: /images/20221120/1079875e628e44028dc5bd0fdc0fce72.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sZWlsZWk2NjY_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201220213451205.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sZWlsZWk2NjY=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sZWlsZWk2NjY_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2020122021355493.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sZWlsZWk2NjY=,size_16,color_FFFFFF,t_70