擴展group by語句

来源:https://www.cnblogs.com/yongestcat/archive/2020/03/23/12550374.html

學習自《劍破冰山 Oracle開發藝術》第五章 報表開發之擴展GROUP BY對於簡單group by語句很難對複雜維度進行分析,難以達到實際生產的複雜報表需求,group by的擴展特性就需要了,union語句也可以達到需求但是sql複雜且效率低1 rollup多維彙總rollup,分組先進行常規... ...


學習自《劍破冰山 Oracle開發藝術》第五章 報表開發之擴展GROUP BY


對於簡單group by語句很難對複雜維度進行分析,難以達到實際生產的複雜報表需求,group by的擴展特性就需要了,union語句也可以達到需求但是sql複雜且效率低

1 rollup多維彙總

rollup,分組先進行常規分組,然後在此基礎上,通過將列從右向左移動,然後進行更高一級的小計,最後合計,註意rollup分組和列的順序相關

指定n列,有n+1種分組方式

部分rollup可以剔除某些不需要的小計和合計

例子

[[email protected] ~]$ sqlplus scott/tiger;

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 23 10:31:24 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

10:31:24 [email protected]> set autotrace on
10:31:30 [email protected]> SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY ROLLUP(a.dname,b.job);

DNAME          JOB          SUM_SAL
-------------- --------- ----------
SALES          CLERK            950
SALES          MANAGER         2850
SALES          SALESMAN        5600
SALES                          9400
RESEARCH       CLERK           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
RESEARCH                      10875
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000
ACCOUNTING                     8750
                              29025

13 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3067950682

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |    14 |   392 |     7  (29)| 00:00:01 |
|   1 |  SORT GROUP BY ROLLUP         |         |    14 |   392 |     7  (29)| 00:00:01 |
|   2 |   MERGE JOIN                  |         |    14 |   392 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |         |    14 |   210 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | EMP     |    14 |   210 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"."DEPTNO"="B"."DEPTNO")
       filter("A"."DEPTNO"="B"."DEPTNO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        913  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         13  rows processed

10:31:34 [email protected]> 

可以看出僅僅dept和emp表均僅掃描一次,而如果是union來寫就會多次重覆掃描,效率低

通過執行計劃看到有個隱藏操作SORT GROUP BY ROLLUP ,顯示結果有序,一般還是要顯示排序的,預設的排序不一定符合業務需求

rollup分組具有方向性

如果使用hint:expand_gset_to_union,則優化器會將rollup轉換為對應的union all操作,其他的grouping sets、cube也可以


部分rollup分組,將不需要小計的列從rollup拿出到group by中即可,當然合計也沒有了

例子

10:31:34 [email protected]> set autotrace off
10:43:49 [email protected]> SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY to_char(b.hiredate,'yyyy'),a.dname,ROLLUP(b.job);

HIRE DNAME          JOB          SUM_SAL
---- -------------- --------- ----------
1980 RESEARCH       CLERK            800
1980 RESEARCH                        800
1981 SALES          CLERK            950
1981 SALES          MANAGER         2850
1981 SALES          SALESMAN        5600
1981 SALES                          9400
1981 RESEARCH       ANALYST         3000
1981 RESEARCH       MANAGER         2975
1981 RESEARCH                       5975
1981 ACCOUNTING     MANAGER         2450
1981 ACCOUNTING     PRESIDENT       5000
1981 ACCOUNTING                     7450
1982 ACCOUNTING     CLERK           1300
1982 ACCOUNTING                     1300
1987 RESEARCH       CLERK           1100
1987 RESEARCH       ANALYST         3000
1987 RESEARCH                       4100

17 rows selected.

Elapsed: 00:00:00.01
10:43:53 [email protected]> 

2 cube交叉報表

cube分組可以實現更精細複雜的統計,對不同維度的所以可能進行分析,生成交叉報表,cube分組,是從n列中先進行合計,即一個列不取,然後小計,即取1列到n-1列,最後n列全取,即標準分組

因為包含所有可能的組合,所以結果與列的順序無關,列順序僅僅影響預設的隱藏排序而已,如果用了顯示排序則無所謂了

cube分組增加一列,可能結果是指數級的增長,分組種類2的n次方

語法類似,例子

11:02:40 [email protected]> set autotrace on
11:02:48 [email protected]>  SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY CUBE(a.dname,b.job);

DNAME          JOB          SUM_SAL
-------------- --------- ----------
                              29025
               CLERK           4150
               ANALYST         6000
               MANAGER         8275
               SALESMAN        5600
               PRESIDENT       5000
SALES                          9400
SALES          CLERK            950
SALES          MANAGER         2850
SALES          SALESMAN        5600
RESEARCH                      10875
RESEARCH       CLERK           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
ACCOUNTING                     8750
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000

18 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2382666110

-------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |         |    14 |   392 |     7  (29)| 00:00:01 |
|   1 |  SORT GROUP BY                  |         |    14 |   392 |     7  (29)| 00:00:01 |
|   2 |   GENERATE CUBE                 |         |    14 |   392 |     7  (29)| 00:00:01 |
|   3 |    SORT GROUP BY                |         |    14 |   392 |     7  (29)| 00:00:01 |
|   4 |     MERGE JOIN                  |         |    14 |   392 |     6  (17)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  7 |      SORT JOIN                  |         |    14 |   210 |     4  (25)| 00:00:01 |
|   8 |       TABLE ACCESS FULL         | EMP     |    14 |   210 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("A"."DEPTNO"="B"."DEPTNO")
       filter("A"."DEPTNO"="B"."DEPTNO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1175  bytes sent via SQL*Net to client
        535  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         18  rows processed

11:02:52 [email protected]> 

可以看執行計劃,結果也是有序的


部分cube分組,例子

11:06:24 [email protected]>  SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY a.dname,CUBE(b.job);

DNAME          JOB          SUM_SAL
-------------- --------- ----------
SALES                          9400
SALES          CLERK            950
SALES          MANAGER         2850
SALES          SALESMAN        5600
RESEARCH                      10875
RESEARCH       CLERK           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
ACCOUNTING                     8750
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000

12 rows selected.

Elapsed: 00:00:00.00
11:06:26 [email protected]>

3 grouping sets實現小計

rollup和cube會產生各種標準分組、小計、合計,grouping  sets則只關註指定維度的小計,n列的結果也是n種

如grouping sets(a,b,c)就是group by a、group by b和group by c的結果union all

例子

11:06:26 [email protected]>  set autotrace on
11:12:33 [email protected]> SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY GROUPING SETS( to_char(b.hiredate,'yyyy'),a.dname,b.job);

HIRE DNAME          JOB          SUM_SAL
---- -------------- --------- ----------
                    CLERK           4150
                    SALESMAN        5600
                    PRESIDENT       5000
                    MANAGER         8275
                    ANALYST         6000
     ACCOUNTING                     8750
     RESEARCH                      10875
     SALES                          9400
1987                                4100
1980                                 800
1982                                1300
1981                               22825

12 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2825031421

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |    14 |   448 |    17  (24)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION     |                           |       |       |            |          |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D660D_29B9BB |       |       |            |          |
|   3 |    MERGE JOIN                  |                           |    14 |   504 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT                      |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | PK_DEPT                   |     4 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                  |                           |    14 |   322 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL         | EMP                       |    14 |   322 |     3   (0)| 00:00:01 |
|   8 |   LOAD AS SELECT               | SYS_TEMP_0FD9D660E_29B9BB |       |       |            |          |
|   9 |    HASH GROUP BY               |                           |     5 |    60 |     3  (34)| 00:00:01 |
|  10 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D660D_29B9BB |    14 |   168 |     2   (0)| 00:00:01 |
|  11 |   LOAD AS SELECT               | SYS_TEMP_0FD9D660E_29B9BB |       |       |            |          |
|  12 |    HASH GROUP BY               |                           |     4 |    56 |     3  (34)| 00:00:01 |
|  13 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D660D_29B9BB |    14 |   196 |     2   (0)| 00:00:01 |
|  14 |   LOAD AS SELECT               | SYS_TEMP_0FD9D660E_29B9BB |       |       |            |          |
|  15 |    HASH GROUP BY               |                           |     1 |     8 |     3  (34)| 00:00:01 |
|  16 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D660D_29B9BB |    14 |   112 |     2   (0)| 00:00:01 |
|  17 |   VIEW                         |                           |     5 |   160 |     2   (0)| 00:00:01 |
|  18 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D660E_29B9BB |     5 |    60 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("SYS_TBL_$2$"."DEPTNO"="SYS_TBL_$1$"."DEPTNO")
       filter("SYS_TBL_$2$"."DEPTNO"="SYS_TBL_$1$"."DEPTNO")


Statistics
----------------------------------------------------------
         23  recursive calls
         33  db block gets
         39  consistent gets
          4  physical reads
       2172  redo size
        962  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         12  rows processed

11:12:36 [email protected]> 

執行計劃可以看出,沒有預設排序了,無序,和列的順序也無關


同理部分grouping sets分組,例子

11:12:36 [email protected]> set autotrace off
11:17:03 [email protected]> SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY a.dname,GROUPING SETS(to_char(b.hiredate,'yyyy'),b.job);

DNAME          HIRE JOB          SUM_SAL
-------------- ---- --------- ----------
SALES               MANAGER         2850
SALES               CLERK            950
ACCOUNTING          MANAGER         2450
ACCOUNTING          PRESIDENT       5000
ACCOUNTING          CLERK           1300
RESEARCH            MANAGER         2975
SALES               SALESMAN        5600
RESEARCH            ANALYST         6000
RESEARCH            CLERK           1900
RESEARCH       1981                 5975
SALES          1981                 9400
RESEARCH       1987                 4100
ACCOUNTING     1981                 7450
ACCOUNTING     1982                 1300
RESEARCH       1980                  800

15 rows selected.

Elapsed: 00:00:00.01
11:17:05 [email protected]> 

註意此時的含義有較大的變化

cube、rollup作為grouping sets的參數

grouping sets只提供單列分組,沒有合計功能,如果需要提供合計,則可以將rollup或cube作為參數,例子


11:23:59 [email protected]>  SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY GROUPING sets(rollup(a.dname),ROLLUP(b.job));

DNAME          JOB          SUM_SAL
-------------- --------- ----------
               CLERK           4150
               SALESMAN        5600
               PRESIDENT       5000
               MANAGER         8275
               ANALYST         6000
ACCOUNTING                     8750
RESEARCH                      10875
SALES                          9400
                              29025
                              29025

10 rows selected.

Elapsed: 00:00:00.02
11:24:02 [email protected]> 

問題是產生了兩個合計行,因為rollup或cube作為grouping sets參數,相當於每個rollup或cube操作的union all,等價於image這就很好理解功能了

對於重覆合計,使用distinct剔除即可,另外後面還有特殊的函數可以使用,group_id可以用來剔除重覆分組(和distinct功能是不一樣的)

rollup和cube作為參數也可以混用,而且也可以使用其它擴展功能,如部分分組、複合列分組、連接分組等

rollup和cube不能接受grouping sets作為參數,rollup和cube互相作為參數也不行

4 組合列分組、連接分組、重置列分組

組合列分組、連接分組在複雜報表中用處很大。組合列分組用於剔除不必要的小計保留合計,連接分組按每個分組的笛卡爾積進行操作,分組更多更細。對於常規分組滿足不了的需求可以考慮

組合列即將多個列當做整體對待,下列對比表可以清晰展示不同之處

image連接分組更強大,允許group by後出現多個rollup、cube和grouping sets操作,這樣分組級別更多,報表更精細,實現很複雜的需求image實際上不管是同類型的連接分組還是不通類型的連接分組之間,最後的分組級別種類都是每個擴展分組級別種類的乘積,分組級別是笛卡爾積,比如rollup(a,b),rollup(c),最終3*2=6中分組級別


重覆列分組也就是group by中允許重覆列,比如group by rollup(a,(a,b))、group by a,rollup(a,b)

組合列分組

例子

14:48:13 [email protected]> SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY rollup(a.dname,(to_char(b.hiredate,'yyyy'),b.job));

DNAME          HIRE JOB          SUM_SAL
-------------- ---- --------- ----------
SALES          1981 CLERK            950
SALES          1981 MANAGER         2850
SALES          1981 SALESMAN        5600
SALES                               9400
RESEARCH       1980 CLERK            800
RESEARCH       1981 ANALYST         3000
RESEARCH       1981 MANAGER         2975
RESEARCH       1987 CLERK           1100
RESEARCH       1987 ANALYST         3000
RESEARCH                           10875
ACCOUNTING     1981 MANAGER         2450
ACCOUNTING     1981 PRESIDENT       5000
ACCOUNTING     1982 CLERK           1300
ACCOUNTING                          8750
                                   29025

15 rows selected.

Elapsed: 00:00:00.00
14:48:16 [email protected]> 

組合列分組可以實現部分rollup和部分cube分組類似效果並且加上合計

但是這個也比較麻煩,對於需要cube、rollup合計並剔除部分小計的需求用grouping_id或grouping函數

cube和rollup均可以轉換為對應的grouping sets

當然反向也可以,不過意義不大

連接分組

例子

14:48:16 [email protected]>  SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY rollup(a.dname,b.job),ROLLUP(to_char(b.hiredate,'yyyy'));

DNAME          HIRE JOB          SUM_SAL
-------------- ---- --------- ----------
SALES               CLERK            950
SALES               MANAGER         2850
SALES               SALESMAN        5600
SALES                               9400
RESEARCH            CLERK           1900
RESEARCH            ANALYST         6000
RESEARCH            MANAGER         2975
RESEARCH                           10875
ACCOUNTING          CLERK           1300
ACCOUNTING          MANAGER         2450
ACCOUNTING          PRESIDENT       5000
ACCOUNTING                          8750
                                   29025
RESEARCH       1980 CLERK            800
RESEARCH       1980                  800
               1980                  800
SALES          1981 CLERK            950
SALES          1981 MANAGER         2850
SALES          1981 SALESMAN        5600
SALES          1981                 9400
RESEARCH       1981 ANALYST         3000
RESEARCH       1981 MANAGER         2975
RESEARCH       1981                 5975
ACCOUNTING     1981 MANAGER         2450
ACCOUNTING     1981 PRESIDENT       5000
ACCOUNTING     1981                 7450
               1981                22825
ACCOUNTING     1982 CLERK           1300
ACCOUNTING     1982                 1300
               1982                 1300
RESEARCH       1987 CLERK           1100
RESEARCH       1987 ANALYST         3000
RESEARCH       1987                 4100
               1987                 4100

34 rows selected.

Elapsed: 00:00:00.01
14:57:57 [email protected]> 

相當於兩個rollup的笛卡爾積

理解了之後,利用連接分組,cube可以用rollup轉換,如cube(a,b,c)等於rollup(a),rollup(b),rollup(c),但是對於rollup和grouping sets轉換為cube一般沒啥用

連接分組一般是同類型的,不通類型的連接分組一般不常用

重覆列分組

例子

14:57:57 [email protected]>   SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY a.dname,ROLLUP(a.dname,b.job);

DNAME          JOB          SUM_SAL
-------------- --------- ----------
SALES          CLERK            950
SALES          MANAGER         2850
SALES          SALESMAN        5600
RESEARCH       CLERK           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000
SALES                          9400
RESEARCH                      10875
ACCOUNTING                     8750
SALES                          9400
RESEARCH                      10875
ACCOUNTING                     8750

15 rows selected.

Elapsed: 00:00:00.00
15:07:14 [email protected]> 

沒啥意義的例子,只不過說明語法允許

5 三個擴展分組函數:grouping、grouping_id、group_id

三個擴展分組函數:grouping、grouping_id、group_id在生成有意義的報表、結果進行過濾、排序中有很重要的作用,常用於複雜的報表查詢

註意grouping和grouping_id函數的參數不能是組合列

grouping函數用於製作有意義的報表

grouping_id函數對結果過濾以及排序

group_id函數剔除重覆行

grouping函數

在擴展group by子句來說,null表示小計或者合計,但是如果數據中本來就有null值呢?grouping函數專門處理擴展group by分組中null問題:

    它只接受一個參數,且參數來自rollup、cube、grouping sets中的列。當然也可以在group by而不在上述3個子句的列,不過結果肯定是0,沒有意義

    grouping函數對於小計或合計的列返回1,否則返回0。用於區別是否原始數據中含null,常與decode一起使用。當然也可以確定分組級別從而過濾一些行,不過會很煩,一般用grouping_id替代

例子

15:34:01 [email protected]>  SELECT decode(GROUPING(a.dname),1,'全部部門',a.dname) dname,decode(grouping(b.mgr),1,'全部老闆',b.mgr) mgr,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY ROLLUP(a.dname,b.mgr);

DNAME          MGR                                         SUM_SAL
-------------- ---------------------------------------- ----------
SALES          7698                                           6550
SALES          7839                                           2850
SALES          全部老闆                                       9400
RESEARCH       7566                                           6000
RESEARCH       7788                                           1100
RESEARCH       7839                                           2975
RESEARCH       7902                                            800
RESEARCH       全部老闆                                      10875
ACCOUNTING                                                    5000
ACCOUNTING     7782                                           1300
ACCOUNTING     7839                                           2450
ACCOUNTING     全部老闆                                       8750
全部部門       全部老闆                                      29025

13 rows selected.

Elapsed: 00:00:00.01
15:34:12 [email protected]> 

grouping_id函數

用於過濾分組級別和排序結果

可以接受多個參數,來自rollup、cube、grouping sets中的列,按列從左往右順序計算,是分組列則0,是小計或合計列為1,然後組合成為一個二進位數字叫做位向量,位向量轉化為10進位即最後的結果,代表分組級別,如cube(a,b),那麼grouping_id(a,b)代表的如下

imagegrouping_id的好處是可以對多列進行計算得到分組級別

例子

15:46:26 [email protected]>  SELECT a.dname,b.mgr,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY ROLLUP(a.dname,b.mgr,b.job) HAVING grouping_id(a.dname,b.mgr,b.job) IN (0,7);

DNAME                 MGR JOB          SUM_SAL
-------------- ---------- --------- ----------
SALES                7698 CLERK            950
SALES                7698 SALESMAN        5600
SALES                7839 MANAGER         2850
RESEARCH             7566 ANALYST         6000
RESEARCH             7788 CLERK           1100
RESEARCH             7839 MANAGER         2975
RESEARCH             7902 CLERK            800
ACCOUNTING                PRESIDENT       5000
ACCOUNTING           7782 CLERK           1300
ACCOUNTING           7839 MANAGER         2450
                                         29025

11 rows selected.

Elapsed: 00:00:00.00
15:46:29 [email protected]> 

group_id函數

group_id無參數,因為擴展group by子句允許多種複雜分組操作,有時候為了實現複雜報表,可能出現重覆統計,而group_id函數可以區分重覆分組結果,第一次出現為0,以後每次出現增1,group_id在select中出現沒啥意義,通常用於having子句剔除重覆統計

例子

15:46:29 [email protected]>  SELECT a.dname,b.job,SUM(b.sal) sum_sal,group_id() gi FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY GROUPING SETS(ROLLUP(a.dname),ROLLUP(b.job)) HAVING group_id()=0;

DNAME          JOB          SUM_SAL         GI
-------------- --------- ---------- ----------
               CLERK           4150          0
               SALESMAN        5600          0
               PRESIDENT       5000          0
               MANAGER         8275          0
               ANALYST         6000          0
ACCOUNTING                     8750          0
RESEARCH                      10875          0
SALES                          9400          0
                              29025          0

9 rows selected.

Elapsed: 00:00:00.01
15:55:55 [email protected]>

您的分享是我們最大的動力!

更多相關文章
  • 在linux和windows中都有路由配置的方法,在日常開發中也經常遇到,比如自己公司各個網站內部開了openvpn這種應用以後,路由配置不好有可能導致改走vpn的不走,網路卡頓甚不能正常上網 1.命令格式 看不懂不要緊,看下麵常用命令那的用法 2.常用選項和參數說明 2.1 選項說明 2.2 參數 ...
  • 前面系列文章講解了Linux下通過文件傳輸、文件共用、郵件系統來分享和獲取資源,本文講解網路資源獲取和共用的另外一種形式,通過Apache服務程式來提供Web服務。 ...
  • 為什麼提交到github的commit都是一個作者 "參考鏈接" "重要知識點講解" 問題如下所示 git是分散式去中心化的管理系統 ssh秘鑰對生成。並把id_rsa.pub加入github.com中(這個過程只是建立一個可以互訪的通道而已) 只是建立了github倉庫之間可以互訪 到底是本地哪個 ...
  • https://www.cnblogs.com/tan-chao/p/11023181.html 簡介 MongoDB[1] 是一個基於分散式文件存儲的資料庫。由C 語言編寫。旨在為WEB應用提供可擴展的高性能數據存儲解決方案。 MongoDB[2] 是一個介於關係資料庫和非關係資料庫之間的產品,是 ...
  • 在一張表裡有多個索引 , 我們where欄位里條件有多個索引的值 , 那麼究竟使用的哪個呢? 我們可以使用explain來查看 , 其中的key_len欄位可以看得出來 比如下麵這條sql explain select * from ent_calendar_diary where email='x ...
  • 分享攢了多年的mssql腳本 腳本類別包括:備份還原表分區常用函數錯誤日誌定時自動抓取耗時SQL並歸檔發郵件腳本模塊鏡像批量腳本資料庫收縮資料庫損壞資料庫賬號統計資料庫大小性能作業 腳本數量:54個 github地址:https://github.com/xiaohuazi123/mssql-scr ...
  • SQL 包含以下 4 部分: 1 數據定義語言(DDL):DROP、CREATE、ALTER 等語句。 2 數據操作語言(DML):INSERT(插入)、UPDATE(修改)、DELETE(刪除)語句。 3 數據查詢語言(DQL):SELECT 語句。 4 數據控制語言(DCL): GRANT、RE ...
  • 第一個就是使用優化查詢的方法。這個在前期的內容中有具體說明,這裡不再做說明。 第二、這裡簡要說明一個以下幾個方法: 主從複製、讀寫分離、負載均衡 目前,大部分的主流關係型資料庫都提供了主從複製的功能,通過配置兩台(或多台)資料庫的主從關係,可以將一臺資料庫伺服器的數據更新同步到另一臺伺服器上。網站可 ...
一周排行
  • 一、引言 按照專用隊列解釋: MachineName\Private$\QueueName,只針對於本機的程式才可以調用的隊列,有些情況下為了安全起見定義為私有隊列。所以剛開始的時候認為,要想訪問遠程消息隊列,只能使用公共隊列。但是後來發現,公共隊列依賴Domain Controller(域控),在 ...
  • 本文只對api介面,header請求參數進行簡單驗證,起到拋磚引玉使用,需要深入驗證,請自行擴展 項目目錄結構如圖 中間件類 using ApiMiddleware.Common.DataEnityModel; using ApiMiddleware.Common.DbContext; using ...
  • 前言:由於公司占時沒有運維,出於微服務的需要,Apollo只能先裝在windows 阿裡雲上跑起來,由於環境及網路等問題,在安裝過程中遇到很多坑,算是一個個坑填完後,最終實現。 一. java jdk環境 java jdk 1.8下載地址: https://www.oracle.com/java/t ...
  • 前言 nuget 是 .net 的常用包管理器,目前已經內置到 Visual Studio 2012 以後的版本。大多數 .net 包都托管在 nuget.org,包括 .net core 框架基礎包,得益於 .net core 的模塊化設計,很多非核心包都可以進行一定程度的獨立升級。 製作並上傳 ...
  • 簡單的介紹一下集合,通俗來講就是用來保管多個數據的方案。比如說我們是一個公司的倉庫管理,公司有一堆貨物需要管理,有同類的,有不同類的,總而言之就是很多、很亂。我們對照集合的概念對倉庫進行管理的話,那麼 數組就是將一堆貨整整齊齊的碼在倉庫的某個地方,普通列表也是如此;Set就是在倉庫里有這麼一個貨架, ...
  • 中間件分類 ASP.NET Core 中間件的配置方法可以分為以上三種,對應的Helper方法分別是:Run(), Use(), Map()。 Run(),使用Run調用中間件的時候,會直接返回一個響應,所以後續的中間件將不會被執行了。 Use(),它會對請求做一些工作或處理,例如添加一些請求的上下 ...
  • 字元串的常用操作 很好理解 字元串可以用 ' + ' 連接,或者乘一個常數重覆輸出字元串 字元串的索引操作 通過一對中括弧可以找到字元串中的某個字元 可以通過正負數雙向操作噢 用一個中括弧來實現 為什麼沒有-0??去清醒腦子想想 -0 和 0 有差嗎? 還有一個切片操作 就像切菜那樣簡單,同樣是中括 ...
  • title: Java基礎語法(3) 運算符 blog: "CSDN" data: "Java學習路線及視頻" 1.算術運算符 算術運算符的註意問題 如果對負數取模,可以把模數負號忽略不記,如:5% 2=1。 但被模數是負數則不可忽略。此外,取模運算的結果不一定總是整數。 對於除號“/”,它的整數除 ...
  • 下麵是互相轉換的代碼: 有想要瞭解更多關於python知識的請在下方評論或私信小編 ...
  • 引言 構建分散式系統並不容易。然而,人們日常所使用的應用大多基於分散式系統,在短時間內依賴於分散式系統的現狀並不會改變。ApacheZooKeeper旨在減輕構建健壯的分散式系統的任務。ZooKeeper基於 分散式計算的核心概念而設計,主要目的是給開發人員提供一套容易理解和開發的介面,從而簡化分佈 ...
x