Oracle分组函数

SQL> --avg,count,max,min,sum举例
SQL> --统计员工个数
SQL> select count(*) from emp;

  COUNT(*)                                                                                                             
----------                                                                                                             
        15                                                                                                             

SQL> --求平均工资，最高工资，最低工资，工资总额
SQL> select avg(sal) 平均工资,max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总额
  2  from emp;

  平均工资   最高工资   最低工资   工资总额                                                                            
---------- ---------- ---------- ----------                                                                            
2073.21429       5000        800      29025                                                                            

SQL> select count(*), count(empno),count(comm) from emp;

  COUNT(*) COUNT(EMPNO) COUNT(COMM)                                                                                    
---------- ------------ -----------                                                                                    
        15           15           4                                                                                    

SQL> --分组函数会自动去掉结果集中的null值，然后在做计算
SQL> --分组数据: group by
SQL> --求每个部门的员工数
SQL> select deptno,count(*)
  2  from emp
  3  group by deptno;

    DEPTNO   COUNT(*)                                                                                                  
---------- ----------                                                                                                  
        30          6                                                                                                  
                    1                                                                                                  
        20          5                                                                                                  
        10          3                                                                                                  

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                  
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                  
      7369 SMITH      CLERK           7902 17-12月-80       800                    20                                  
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                  
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30                                  
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20                                  
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30                                  
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                  
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10                                  
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20                                  
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                  
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                  
      7876 ADAMS      CLERK           7788 13-7月 -87      1100                    20                                  

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                  
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                  
      7900 JAMES      CLERK           7698 03-12月-81       950                    30                                  
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20                                  
      7934 MILLER     CLERK           7782 23-1月 -82      1300                    10                                  
      1234 Tom_123                                                                                                     

已选择15行。

SQL> delete from emp where empno=1234;

已删除 1 行。

SQL> commit;

提交完成。

SQL> select deptno,count(*)
  2  from emp
  3  group by deptno;

    DEPTNO   COUNT(*)                                                                                                  
---------- ----------                                                                                                  
        30          6                                                                                                  
        20          5                                                                                                  
        10          3                                                                                                  

SQL> ed
已写入 file afiedt.buf

  1  select deptno,count(*)
  2  from emp
  3  group by deptno
  4* order by 1
SQL> /

    DEPTNO   COUNT(*)                                                                                                  
---------- ----------                                                                                                  
        10          3                                                                                                  
        20          5                                                                                                  
        30          6                                                                                                  

SQL> --group by作用于多列
SQL> --统计各部门员工的人数，要求不各部门按工种统计
SQL> select deptno,job,count(*)
  2  from emp
  3  group by deptno,job;

    DEPTNO JOB         COUNT(*)                                                                                        
---------- --------- ----------                                                                                        
        20 CLERK              2                                                                                        
        30 SALESMAN           4                                                                                        
        20 MANAGER            1                                                                                        
        30 CLERK              1                                                                                        
        10 PRESIDENT          1                                                                                        
        30 MANAGER            1                                                                                        
        10 CLERK              1                                                                                        
        10 MANAGER            1                                                                                        
        20 ANALYST            2                                                                                        

已选择9行。

SQL> ed
已写入 file afiedt.buf

  1  select deptno,job,count(*)
  2  from emp
  3  group by deptno,job
  4* order by 1
SQL> /

    DEPTNO JOB         COUNT(*)                                                                                        
---------- --------- ----------                                                                                        
        10 CLERK              1                                                                                        
        10 MANAGER            1                                                                                        
        10 PRESIDENT          1                                                                                        
        20 ANALYST            2                                                                                        
        20 CLERK              2                                                                                        
        20 MANAGER            1                                                                                        
        30 CLERK              1                                                                                        
        30 MANAGER            1                                                                                        
        30 SALESMAN           4                                                                                        

已选择9行。

SQL> ed
已写入 file afiedt.buf

  1  select deptno,job,count(*)
  2  from emp
  3  group by deptno
  4* order by 1
SQL> /
select deptno,job,count(*)
              *
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式

SQL> --查询部门人数大于3的部门
SQL> select deptno, count(*)
  2  from emp
  3  group by deptno
  4  having count(*)>3;

    DEPTNO   COUNT(*)                                                                                                  
---------- ----------                                                                                                  
        30          6                                                                                                  
        20          5                                                                                                  

SQL> --having: 在分组的基础上，再对结果集进行筛选
SQL> --group by的增强:
SQL> --语法格式: group by rollup(a,b)
SQL> /*
SQL> group by rollup(deptno,job)
SQL> 等同于:
SQL> group by deptno,job
SQL> +
SQL> group by deptno
SQL> +
SQL> group by null*/
SQL>
SQL> select deptno,job,sum(sal)
  2  from emp
  3  group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL)                                                                                        
---------- --------- ----------                                                                                        
        10 CLERK           1300                                                                                        
        10 MANAGER         2450                                                                                        
        10 PRESIDENT       5000                                                                                        
        10                 8750                                                                                        
        20 CLERK           1900                                                                                        
        20 ANALYST         6000                                                                                        
        20 MANAGER         2975                                                                                        
        20                10875                                                                                        
        30 CLERK            950                                                                                        
        30 MANAGER         2850                                                                                        
        30 SALESMAN        5600                                                                                        

    DEPTNO JOB         SUM(SAL)                                                                                        
---------- --------- ----------                                                                                        
        30                 9400                                                                                        
                          29025                                                                                        

已选择13行。

SQL> break on deptno skip 2
SQL> --break on deptno:不同的部门号才显示
SQL> --skip 2:不同的部门号之间空两行
SQL> /

    DEPTNO JOB         SUM(SAL)                                                                                        
---------- --------- ----------                                                                                        
        10 CLERK           1300                                                                                        
           MANAGER         2450                                                                                        
           PRESIDENT       5000                                                                                        
                           8750                                                                                        
                                                                                                                       
                                                                                                                       
        20 CLERK           1900                                                                                        
           ANALYST         6000                                                                                        
           MANAGER         2975                                                                                        
                          10875                                                                                        
                                                                                                                       

    DEPTNO JOB         SUM(SAL)                                                                                        
---------- --------- ----------                                                                                        
                                                                                                                       
        30 CLERK            950                                                                                        
           MANAGER         2850                                                                                        
           SALESMAN        5600                                                                                        
                           9400                                                                                        
                                                                                                                       
                                                                                                                       
                          29025                                                                                        
                                                                                                                       
                                                                                                                       

已选择13行。

SQL> spool off