小編給大家分享一下ROLLUP,CUBE,GROUPING SETS,grouping_id()函數(shù)有什么用，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

創(chuàng)新互聯(lián)主要業(yè)務有網(wǎng)站營銷策劃、成都網(wǎng)站制作、網(wǎng)站設計、微信公眾號開發(fā)、微信小程序定制開發(fā)、H5高端網(wǎng)站建設、程序開發(fā)等業(yè)務。一次合作終身朋友，是我們奉行的宗旨；我們不僅僅把客戶當客戶，還把客戶視為我們的合作伙伴，在開展業(yè)務的過程中，公司還積累了豐富的行業(yè)經(jīng)驗、成都全網(wǎng)營銷推廣資源和合作伙伴關系資源，并逐漸建立起規(guī)范的客戶服務和保障體系。 

1.ROLLUP

group by rollup(1,2,3), 可以理解為從右到左以一次少一列的方式依次進行group by。

例如: group by rollup(1,2,3) 則以group by(1,2,3) -> group by(1,2) -> group by(1) -> group by null(最終匯總)的順序進行分組

相當于:

Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,null,null,sum(E) from test;

2.CUBE

group by cube(1,2,3), 需要對每一列的排列組合進行group by

例如: group by cube(1,2,3) 則以 group by(1,2,3) -> (1,2) -> (1,3) -> (2,3) -> (2) -> (3) -> group by null(最終匯總)的順序進行分組

相當于:

Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,C,sum(E) from test group by A,C
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,B,C,sum(E) from test group by B,C
union all
Select null,B,null,sum(E) from test group by B
union all
Select null,null,C,sum(E) from test group by C
union all
Select null,null,null,sum(E) from test;

3.GROUPING SETS

自定義分組方案

group by GROUPING SETS(1,2,3)  = (1),(2),(3) 分別group by

group by grouping sets((1,2),3) = (1,2),(3) 分別group by

4.組合應用

group by A,rollup(A,B)

將對所有group by 后面的集合進行笛卡爾積

因此順序為: (A,(A,B)),(A,A),(A,NULL) = (A,B),(A),(A)

Select A,B,sum(E) from test1 group by A, rollup(A,B);

Select A,B,sum(E) from test1 group by A,B
Union all
Select A,null,sum(E) from test1 group by A
Union all
Select A,null,sum(E) from test1 group by A;

5.GROUPING_ID()

即GROUPING函數(shù)用于區(qū)分分組后的普通行和聚合行。如果是聚合行，則返回1，反之，則是0。

GROUPING_ID是GROUPING的增強版，與GROUPING只能帶一個表達式不同，它能帶多個表達式。

SELECT TO_CHAR (log_date, 'YYYY') year,
         TO_CHAR (log_date, 'Q') quarter,
         TO_CHAR (log_date, 'MM') month,
         employee_id,
         MIN (old_salary),
         MIN (new_salary),
         GROUPING_ID (TO_CHAR (log_date, 'YYYY'),
                      TO_CHAR (log_date, 'Q'),
                      TO_CHAR (log_date, 'MM'))
            gid
    FROM plch_emp_log
GROUP BY ROLLUP (TO_CHAR (log_date, 'YYYY'),
                 TO_CHAR (log_date, 'Q'),
                 TO_CHAR (log_date, 'MM')),
         employee_id;

YEAR     QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY)        GID
-------- -- ---- ----------- --------------- --------------- ----------
2010     1  01           100            1000            1800          0
2010     1               100            1000            1800          1
2010     2  04           100            1800            1900          0
2010     2               100            1800            1900          1
2010     3  09           100            1900            1500          0
2010     3               100            1900            1500          1
2010                     100            1000            1500          3
2011     1  01           100            1500            2500          0
2011     1               100            1500            2500          1
2011     2  04           100            2500            2200          0
2011     2               100            2500            2200          1

YEAR     QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY)        GID
-------- -- ---- ----------- --------------- --------------- ----------
2011                     100            1500            2200          3
                         100            1000            1500          7
2010     1  01           200            1000            1600          0
2010     1  03           200            1600            2500          0
2010     1               200            1000            1600          1
2010     2  05           200            2500            2300          0
2010     2               200            2500            2300          1
2010     3  09           200            2300            3000          0
2010     3               200            2300            3000          1
2010                     200            1000            1600          3
2011     1  02           200            3000            2000          0

YEAR     QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY)        GID
-------- -- ---- ----------- --------------- --------------- ----------
2011     1               200            3000            2000          1
2011     3  07           200            2000            2800          0
2011     3               200            2000            2800          1
2011                     200            2000            2000          3
                         200            1000            1600          7
2010     2  04           300            1000            2000          0
2010     2  05           300            2000            3000          0
2010     2               300            1000            2000          1
2010     4  10           300            3000            2700          0
2010     4               300            3000            2700          1
2010                     300            1000            2000          3

YEAR     QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY)        GID
-------- -- ---- ----------- --------------- --------------- ----------
2011     1  02           300            2700            2500          0
2011     1               300            2700            2500          1
2011     3  09           300            2500            2900          0
2011     3               300            2500            2900          1
2011                     300            2500            2500          3
                         300            1000            2000          7

39 rows selected.

以上是“ROLLUP,CUBE,GROUPING SETS,grouping_id()函數(shù)有什么用”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關注創(chuàng)新互聯(lián)行業(yè)資訊頻道！