擴展group by語句

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

學習自《劍破冰山 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可以剔除某些不需要的小計和合計

例子

[oracle@localhost ~]$ 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 SCOTT@edw> set autotrace on
10:31:30 SCOTT@edw> 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 SCOTT@edw>

可以看出僅僅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 SCOTT@edw> set autotrace off
10:43:49 SCOTT@edw> 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 SCOTT@edw>

2 cube交叉報表

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

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

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

語法類似，例子

11:02:40 SCOTT@edw> set autotrace on
11:02:48 SCOTT@edw>  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 SCOTT@edw>

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


部分cube分組，例子

11:06:24 SCOTT@edw>  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 SCOTT@edw>

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 SCOTT@edw>  set autotrace on
11:12:33 SCOTT@edw> 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 SCOTT@edw>

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


同理部分grouping sets分組，例子

11:12:36 SCOTT@edw> set autotrace off
11:17:03 SCOTT@edw> 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 SCOTT@edw>

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

cube、rollup作為grouping sets的參數

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


11:23:59 SCOTT@edw>  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 SCOTT@edw>

問題是產生了兩個合計行，因為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 SCOTT@edw> 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 SCOTT@edw>

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

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

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

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

連接分組

例子

14:48:16 SCOTT@edw>  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 SCOTT@edw>

相當於兩個rollup的笛卡爾積

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

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

重覆列分組

例子

14:57:57 SCOTT@edw>   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 SCOTT@edw>

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

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 SCOTT@edw>  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 SCOTT@edw>

grouping_id函數

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

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

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

例子

15:46:26 SCOTT@edw>  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 SCOTT@edw>

group_id函數

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

例子

15:46:29 SCOTT@edw>  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 SCOTT@edw>

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

-Advertisement-
Play Games
更多相關文章
  • (8)route命令（每周一個linux命令系列）
    在linux和windows中都有路由配置的方法，在日常開發中也經常遇到，比如自己公司各個網站內部開了openvpn這種應用以後，路由配置不好有可能導致改走vpn的不走，網路卡頓甚不能正常上網 1.命令格式 看不懂不要緊，看下麵常用命令那的用法 2.常用選項和參數說明 2.1 選項說明 2.2 參數 ...
  • linux入門系列18--Web服務之Apache服務1
    前面系列文章講解了Linux下通過文件傳輸、文件共用、郵件系統來分享和獲取資源，本文講解網路資源獲取和共用的另外一種形式，通過Apache服務程式來提供Web服務。 ...
  • git提交更改都是一個作者
    為什麼提交到github的commit都是一個作者 "參考鏈接" "重要知識點講解" 問題如下所示 git是分散式去中心化的管理系統 ssh秘鑰對生成。並把id_rsa.pub加入github.com中（這個過程只是建立一個可以互訪的通道而已） 只是建立了github倉庫之間可以互訪 到底是本地哪個 ...
  • MongoDB簡介、特點、原理、使用場景、應用案例
    https://www.cnblogs.com/tan-chao/p/11023181.html 簡介 MongoDB[1] 是一個基於分散式文件存儲的資料庫。由C 語言編寫。旨在為WEB應用提供可擴展的高性能數據存儲解決方案。 MongoDB[2] 是一個介於關係資料庫和非關係資料庫之間的產品，是 ...
  • [MySQL] 查看explain中的key_len判斷究竟使用了哪個索引
    在一張表裡有多個索引 , 我們where欄位里條件有多個索引的值 , 那麼究竟使用的哪個呢? 我們可以使用explain來查看 , 其中的key_len欄位可以看得出來 比如下麵這條sql explain select * from ent_calendar_diary where email='x ...
  • 分享攢了多年的mssql腳本
    分享攢了多年的mssql腳本 腳本類別包括：備份還原表分區常用函數錯誤日誌定時自動抓取耗時SQL並歸檔發郵件腳本模塊鏡像批量腳本資料庫收縮資料庫損壞資料庫賬號統計資料庫大小性能作業 腳本數量：54個 github地址：https://github.com/xiaohuazi123/mssql-scr ...
  • MySQL 數據定義語言（DDL）
    SQL 包含以下 4 部分： 1 數據定義語言（DDL）：DROP、CREATE、ALTER 等語句。 2 數據操作語言（DML）：INSERT（插入）、UPDATE（修改）、DELETE（刪除）語句。 3 數據查詢語言（DQL）：SELECT 語句。 4 數據控制語言（DCL）: GRANT、RE ...
  • 如果有一個特別大的訪問量到資料庫上，怎麼做優化？主從複製、讀寫分離
    第一個就是使用優化查詢的方法。這個在前期的內容中有具體說明，這裡不再做說明。 第二、這裡簡要說明一個以下幾個方法： 主從複製、讀寫分離、負載均衡 目前，大部分的主流關係型資料庫都提供了主從複製的功能，通過配置兩台（或多台）資料庫的主從關係，可以將一臺資料庫伺服器的數據更新同步到另一臺伺服器上。網站可 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類