oracle group by子句的几种用法

女爷i 2022-04-14 05:37 306阅读 0赞

**Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。**

**如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。**

**如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。**

** **

1.       **普通的group by**

select trade_date, deal_type, sum(turnover)
    
                 from test t
    
     where t.trade_date >= date '2013-08-10'
    
     group by trade_date, deal_type;

![f7814257-dcd4-3e39-ba1a-caea729456cb.png][]

2.       **使用group by rollup()单列**

select trade_date, sum(turnover)
    
                    from test t
    
                   where t.trade_date >= date '2013-08-10'
    
            group by rollup(trade_date);

![6937805e-c915-323f-b8c7-01ce088c1753.png][]  
 可以看到，在最后一行做了汇总。

3.     ** ** **使用group by rollup()多列**

select trade_date, deal_type, sum(turnover)
    
                   from test t
    
     where t.trade_date >= date '2013-08-10'
    
     group by rollup(trade_date, deal_type);

![b8c1e041-bf60-3bae-a6cb-7cade2d5f158.png][]  
 从上图可以看到:

\#1.根据trade\_date做了汇总；

\#2.最后一行做了总的汇总。

按照rollup()的语法，是不会对deal\_type做group by的。上面的SQL做了（trade\_date,deal\_type）的group by；（trade\_date）的group by；以及对全表进行group by 操作。

如果是rollup()3列呢？

select trade_date, deal_type, deal_sub_type, sum(turnover)
    
                    from test t
    
                   where t.trade_date >= date '2013-08-10'
    
                   group by rollup(deal_type, trade_date, deal_sub_type)
    
            order by 1, 2, 3;

![85b49c0a-920d-35a5-a082-23047d37963d.png][]

依次做了下面的group by操作：

\#1.（deal\_type，trade\_date，deal\_sub\_type）；这个不用解释，上图很明显。

\#2.（deal\_type，trade\_date）；第2行、第4行、第7行、第9行、第11行、第13行。

\#3.（deal\_type）。第14到第17行。

第18行是总的一个汇总。

4**.****      ** **使用cube()单列**

select trade_date, sum(turnover)
    
                                   from test t
    
                    where t.trade_date >= date '2013-08-10'
    
             group by cube(trade_date);

![4d54444f-fa60-3822-ba5a-abe1fe1329d5.png][]

跟rollup()对比，你会发现rollup()是在最后一行汇总，cube()是在第1行。

如果想在最后一行汇总，可以使用order by trade\_date nulls last排序。

如下：

select trade_date, sum(turnover)
    
                                   from test t
    
                    where t.trade_date >= date '2013-08-10'
    
                    group by cube(trade_date)
    
       order by trade_date nulls last;

5.       **使用cube()多列**

select trade_date, deal_type, sum(turnover)
    
                    from test t
    
                   where t.trade_date >= date '2013-08-10'
    
                   group by cube(trade_date, deal_type)
    
            order by 1 nulls last;

![9b10f7ef-0934-3215-93b1-50de75a65842.png][]

“order by 1 nulls last”中的1当然指的就是你查询的第1列，即trade\_date了。

通过上图我们可以发现这样几点：

\#1.对整体group by了。

\#2.对trade\_date进行group by了。

\#3.对deal\_type进行group by了。

\#4.对整体进行汇总。

6.       使用grouping和grouping\_id来标记group by的结果

select grouping(trade_date),
    
                                                                    trade_date,
    
                                                                    grouping(deal_type),
    
                                                                    deal_type,
    
                                                                    sum(turnover)
    
                                   from test t
    
                    where t.trade_date >= date '2013-08-10'
    
                    group by cube(trade_date, deal_type)
    
             order by 1 nulls last;

![50a89026-e5d4-322d-b615-9e908b9c71bc.png][]

通过上图你可以发现，如果列的值为空，则显示的值为1；否则显示的0.

显示为1的就是合计的列，由此我们可以使用grouping\_id来标识group by后的结果。

select decode(grouping_id(trade_date), 1, '合计', trade_date),
    
                                                                                   decode(grouping_id(deal_type), 1, '合计', deal_type),
    
                                                                                   sum(turnover)
    
                                    from test t
    
                                   where t.trade_date >= date '2013-08-10'
    
                                   group by cube(trade_date, deal_type)
    
                            order by 1 nulls last;

![8fb42d46-b5c2-34e5-86ed-1cb146173bed.png][]

原来合计的列显示的空白，现在都显示的“合计”。

7.       **使用grouping sets**

select trade_date, deal_type, sum(turnover)
    
                                   from test t
    
                    where t.trade_date >= date '2013-08-10'
    
                    group by grouping sets(trade_date, deal_type)
    
             order by 1 nulls last;

![4eb40bbf-2d11-3328-81a5-0ded27e5ca75.png][]

你可以看到，上面的效果不正等同于使用union all吗？

select trade_date, null, sum(turnover)
    
                                    from test t
    
                                   where t.trade_date >= date '2013-08-10'
    
                                   group by trade_date
    
                    union all
    
                    select null, deal_type, sum(turnover)
    
                                    from test t
    
                                   where t.trade_date >= date '2013-08-10'
    
                            group by deal_type;

[f7814257-dcd4-3e39-ba1a-caea729456cb.png]: /images/20220414/48c27bcbe735428dbbe41ade137575cd.png
[6937805e-c915-323f-b8c7-01ce088c1753.png]: /images/20220414/0931d16a6de34752a3d0c572780d007d.png
[b8c1e041-bf60-3bae-a6cb-7cade2d5f158.png]: /images/20220414/3249bff8d13f4fdea035a507cc1530e9.png
[85b49c0a-920d-35a5-a082-23047d37963d.png]: /images/20220414/92ac9ae0ba774169b5a478730ea1dd23.png
[4d54444f-fa60-3822-ba5a-abe1fe1329d5.png]: /images/20220414/e6c83a6af9604fad9350601ccadd1ea3.png
[9b10f7ef-0934-3215-93b1-50de75a65842.png]: /images/20220414/66f5c632c02b42e1a637a6d0c3500059.png
[50a89026-e5d4-322d-b615-9e908b9c71bc.png]: /images/20220414/48ef3b6db8004a58841c898c5eaf1cd3.png
[8fb42d46-b5c2-34e5-86ed-1cb146173bed.png]: /images/20220414/d51c060f93584792a4431873b0ed8155.png
[4eb40bbf-2d11-3328-81a5-0ded27e5ca75.png]: /images/20220414/bb299da09e3f47ef976dbd88d94580bd.png