oracle 数据分组

1.使用having子句限制分组显示结果

SQL> select deptno,avg(sal),max(sal) from emp
  2  group by deptno
  3  having avg(sal)<2500;

    DEPTNO   AVG(SAL)   MAX(SAL)                                                
---------- ---------- ----------                                                
        20       2175       3000                                                
        30 1566.66667       2850   

2.rollup和cube操作符

示例一：使用rollup

SQL> select deptno,job,avg(sal) from emp
  2  group by rollup(deptno,job);

    DEPTNO JOB         AVG(SAL)                                                 
---------- --------- ----------                                                 
        10 CLERK           1300                                                 
        10 MANAGER         2450                                                 
        10 PRESIDENT       5000                                                 
        10           2916.66667                                                 
        20 CLERK            950                                                 
        20 ANALYST         3000                                                 
        20 MANAGER         2975                                                 
        20                 2175                                                 
        30 CLERK            950                                                 
        30 MANAGER         2850                                                 
        30 SALESMAN        1400                                                 
        30           1566.66667                                                 
                     2073.21429                                                 

13 rows selected.

示例二：使用cube

SQL> select deptno,job,avg(sal) from emp
  2  group by cube(deptno,job);

    DEPTNO JOB         AVG(SAL)                                                 
---------- --------- ----------                                                 
                     2073.21429                                                 
           CLERK         1037.5                                                 
           ANALYST         3000                                                 
           MANAGER   2758.33333                                                 
           SALESMAN        1400                                                 
           PRESIDENT       5000                                                 
        10           2916.66667                                                 
        10 CLERK           1300                                                 
        10 MANAGER         2450                                                 
        10 PRESIDENT       5000                                                 
        20                 2175                                                 
        20 CLERK            950                                                 
        20 ANALYST         3000                                                 
        20 MANAGER         2975                                                 
        30           1566.66667                                                 
        30 CLERK            950                                                 
        30 MANAGER         2850                                                 

    DEPTNO JOB         AVG(SAL)                                                 
---------- --------- ----------                                                 
        30 SALESMAN        1400                                                 

18 rows selected.

3.grouping 函数用于确定统计结果是否用到了特定列

SQL> select deptno,job,avg(sal),grouping(deptno),grouping(job)
  2  from emp group by rollup(deptno,job);

    DEPTNO JOB         AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)                  
---------- --------- ---------- ---------------- -------------                  
        10 CLERK           1300                0             0                  
        10 MANAGER         2450                0             0                  
        10 PRESIDENT       5000                0             0                  
        10           2916.66667                0             1                  
        20 CLERK            950                0             0                  
        20 ANALYST         3000                0             0                  
        20 MANAGER         2975                0             0                  
        20                 2175                0             1                  
        30 CLERK            950                0             0                  
        30 MANAGER         2850                0             0                  
        30 SALESMAN        1400                0             0                  
        30           1566.66667                0             1                  
                     2073.21429                1             1                  

13 rows selected.

4.grouping sets可以合并多个分组的统计结果

示例：显示部门平均工资和岗位平均工资

SQL> select deptno,job,avg(sal) from emp
  2  group by grouping sets(deptno,job);

    DEPTNO JOB         AVG(SAL)                                                 
---------- --------- ----------                                                 
           ANALYST         3000                                                 
           CLERK         1037.5                                                 
           MANAGER   2758.33333                                                 
           PRESIDENT       5000                                                 
           SALESMAN        1400                                                 
        10           2916.66667                                                 
        20                 2175                                                 
        30           1566.66667                                                 

8 rows selected.