oracle数据库的多行函数

来源:互联网 时间:1970-01-01

SQL> --统计一个月要发的基本工资总数
SQL> select sum(sal) from emp;

  SUM(SAL)                                                                      
----------                                                                      
     37075                                                                      

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                      
----------                                                                      
      9527 余里                                                                 
        40                                                                      
                                                                                
      9528 余里                                                                 
        50                                                                      
                                                                                
      7777 lisi       CODER           7788 06-1月 -00           2000            
        10                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                      
----------                                                                      
      7778 wangwu     CLERK           7788 05-2月 -15           1000          0
        30                                                                      
                                                                                
      7566 JONES      MANAGER         7839 02-4月 -81           2975            
        20                                                                      
                                                                                
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
        30                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                      
----------                                                                      
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850            
        30                                                                      
                                                                                
      7782 CLARK      MANAGER         7839 09-6月 -81           2450            
        10                                                                      
                                                                                
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000            
        20                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                      
----------                                                                      
      7839 KING       PRESIDENT            17-11月-81           5000            
        10                                                                      
                                                                                
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
        30                                                                      
                                                                                
      7876 ADAMS      CLERK           7788 23-5月 -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          10000            
        10                                                                      
                                                                                

已选择15行。

SQL> set linesize 200;
SQL> set pagesize 20;
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                                                                                                              
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                                                                                              
      9527 余里                                                                         40                                                                                                              
      9528 余里                                                                         50                                                                                                              
      7777 lisi       CODER           7788 06-1月 -00           2000                    10                                                                                                              
      7778 wangwu     CLERK           7788 05-2月 -15           1000          0         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 19-4月 -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 23-5月 -87           1100                    20                                                                                                              
      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          10000                    10                                                                                                              

已选择15行。

SQL> --求每个月的平均工资
SQL> select sum(sal)/count(*) ,avg(sal) from emp;

SUM(SAL)/COUNT(*)   AVG(SAL)                                                                                                                                                                            
----------------- ----------                                                                                                                                                                            
       2471.66667 2851.92308                                                                                                                                                                            

SQL> select count(*) from emp;

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

SQL> --多行函数会缕空
SQL> --可以用nvl函数来干掉多行函数本身的缕空
SQL> select sum(sal)/count(*) ,avg(nvl(sal,0)) from emp;

SUM(SAL)/COUNT(*) AVG(NVL(SAL,0))                                                                                                                                                                       
----------------- ---------------                                                                                                                                                                       
       2471.66667      2471.66667                                                                                                                                                                       

SQL> --求sal最小和comm最大的
SQL> select min(sal) 最小的工资, max(comm) 最大的奖金 from emp;

最小的工资 最大的奖金                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                   
       950       1400                                                                                                                                                                                   

SQL> --分组
SQL> --统计每个部门的员工总数
SQL> select count(*)
  2  from emp
  3  group by deptno;

  COUNT(*)                                                                                                                                                                                              
----------                                                                                                                                                                                              
         5                                                                                                                                                                                              
         4                                                                                                                                                                                              
         1                                                                                                                                                                                              
         1                                                                                                                                                                                              
         4                                                                                                                                                                                              

SQL> ed
已写入 file afiedt.buf

  1  select count(*),detpno
  2  from emp
  3* group by deptno
SQL> /
select count(*),detpno
                *
第 1 行出现错误:
ORA-00904: "DETPNO": 标识符无效


SQL> ed
已写入 file afiedt.buf

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

  COUNT(*)     DEPTNO                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                   
         5         30                                                                                                                                                                                   
         4         20                                                                                                                                                                                   
         1         40                                                                                                                                                                                   
         1         50                                                                                                                                                                                   
         4         10                                                                                                                                                                                   

SQL> --统计一下每一个部门的基本工资的平均数
SQL> select detpno,avg(nvl(sal,0)) 基本工资
  2  from emp
  3  group by deptno;
select detpno,avg(nvl(sal,0)) 基本工资
       *
第 1 行出现错误:
ORA-00904: "DETPNO": 标识符无效


SQL> ed
已写入 file afiedt.buf

  1  select deptno,avg(nvl(sal,0)) 基本工资
  2  from emp
  3* group by deptno
SQL> /

    DEPTNO   基本工资                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                   
        30       1510                                                                                                                                                                                   
        20    2518.75                                                                                                                                                                                   
        40          0                                                                                                                                                                                   
        50          0                                                                                                                                                                                   
        10     4862.5                                                                                                                                                                                   

SQL> --求每个部门的平均奖金
SQL> -- 求他们的平均工资
SQL> select deptno,sal,comm,avg(nvl(sal,0)+nvl(comm,0)) 平均工资
  2  from emp
  3  group by deptno;
select deptno,sal,comm,avg(nvl(sal,0)+nvl(comm,0)) 平均工资
              *
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式


SQL> ed
已写入 file afiedt.buf

  1  select deptno,sal,comm,avg(nvl(sal,0)+nvl(comm,0)) 平均工资
  2  from emp
  3* group by deptno,sal,comm
SQL> /

    DEPTNO        SAL       COMM   平均工资                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
        30        950                   950                                                                                                                                                             
        40                                0                                                                                                                                                             
        30       1500          0       1500                                                                                                                                                             
        20       2975                  2975                                                                                                                                                             
        30       1250       1400       2650                                                                                                                                                             
        30       2850                  2850                                                                                                                                                             
        20       1100                  1100                                                                                                                                                             
        10       2000                  2000                                                                                                                                                             
        10       2450                  2450                                                                                                                                                             
        20       3000                  3000                                                                                                                                                             
        10      10000                 10000                                                                                                                                                             
        50                                0                                                                                                                                                             
        30       1000          0       1000                                                                                                                                                             
        10       5000                  5000                                                                                                                                                             

已选择14行。

SQL> --如果select后面包含了组函数,但是还有一些列没有使用组函数,那么需要将这些列写在group by 后面
SQL> ed
已写入 file afiedt.buf

  1  select deptno,sal,comm,avg(sum(nvl(sal,0)+nvl(comm,0))) 平均工资
  2  from emp
  3* group by deptno,sal,comm
SQL> /
select deptno,sal,comm,avg(sum(nvl(sal,0)+nvl(comm,0))) 平均工资
       *
第 1 行出现错误:
ORA-00937: 不是单组分组函数


SQL> ed
已写入 file afiedt.buf

  1  select deptno,sal,comm,avg(nvl(sal,0)+nvl(comm,0)) 平均工资
  2  from emp
  3* group by deptno,sal,comm
SQL> /

    DEPTNO        SAL       COMM   平均工资                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
        30        950                   950                                                                                                                                                             
        40                                0                                                                                                                                                             
        30       1500          0       1500                                                                                                                                                             
        20       2975                  2975                                                                                                                                                             
        30       1250       1400       2650                                                                                                                                                             
        30       2850                  2850                                                                                                                                                             
        20       1100                  1100                                                                                                                                                             
        10       2000                  2000                                                                                                                                                             
        10       2450                  2450                                                                                                                                                             
        20       3000                  3000                                                                                                                                                             
        10      10000                 10000                                                                                                                                                             
        50                                0                                                                                                                                                             
        30       1000          0       1000                                                                                                                                                             
        10       5000                  5000                                                                                                                                                             

已选择14行。

SQL> select avg(sum(sal)) from emp;
select avg(sum(sal)) from emp
           *
第 1 行出现错误:
ORA-00978: 嵌套分组函数没有 GROUP BY


SQL> ed
已写入 file afiedt.buf

  1* select avg(sum(sal)) from emp group by sum(sal)
SQL> /
select avg(sum(sal)) from emp group by sum(sal)
                                       *
第 1 行出现错误:
ORA-00934: 此处不允许使用分组函数


SQL> ed
已写入 file afiedt.buf

  1* select avg(sum(sal)) from emp group by sal
SQL> /

AVG(SUM(SAL))                                                                                                                                                                                           
-------------                                                                                                                                                                                           
   3089.58333                                                                                                                                                                                           

SQL> ed
已写入 file afiedt.buf

  1* select avg(sum(nvl(sal,0))) from emp group by sal
SQL> /

AVG(SUM(NVL(SAL,0)))                                                                                                                                                                                    
--------------------                                                                                                                                                                                    
          2851.92308                                                                                                                                                                                    

SQL> select sum(sal)/count(*) from emp;

SUM(SAL)/COUNT(*)                                                                                                                                                                                       
-----------------                                                                                                                                                                                       
       2471.66667                                                                                                                                                                                       

SQL> select empno,avg(sum(nvl(sal,0)) from emp group by empno,sal;
select empno,avg(sum(nvl(sal,0)) from emp group by empno,sal
                                 *
第 1 行出现错误:
ORA-00907: 缺失右括号


SQL> ed
已写入 file afiedt.buf

  1* select empno,avg(sum(nvl(sal,0))) from emp group by empno,sal
SQL> /
select empno,avg(sum(nvl(sal,0))) from emp group by empno,sal
       *
第 1 行出现错误:
ORA-00937: 不是单组分组函数


SQL> ed
已写入 file afiedt.buf

  1* select count(empno),avg(sum(nvl(sal,0))) from emp group by empno,sal
SQL> /

COUNT(EMPNO) AVG(SUM(NVL(SAL,0)))                                                                                                                                                                       
------------ --------------------                                                                                                                                                                       
          15           2471.66667                                                                                                                                                                       

SQL> select sum(sal) from emp;

  SUM(SAL)                                                                                                                                                                                              
----------                                                                                                                                                                                              
     37075                                                                                                                                                                                              

SQL> select sal from emp;

       SAL                                                                                                                                                                                              
----------                                                                                                                                                                                              
                                                                                                                                                                                                        
                                                                                                                                                                                                        
      2000                                                                                                                                                                                              
      1000                                                                                                                                                                                              
      2975                                                                                                                                                                                              
      1250                                                                                                                                                                                              
      2850                                                                                                                                                                                              
      2450                                                                                                                                                                                              
      3000                                                                                                                                                                                              
      5000                                                                                                                                                                                              
      1500                                                                                                                                                                                              
      1100                                                                                                                                                                                              
       950                                                                                                                                                                                              
      3000                                                                                                                                                                                              
     10000                                                                                                                                                                                              

已选择15行。

SQL> --求平均进本大于2000的部门
SQL> select deptno,avg(sum(nvl(sal,0))) 部门平均工资
  2  from emp
  3  where avg(sum(nvl(sal,0))) >= 2000
  4  group by deptno;
where avg(sum(nvl(sal,0))) >= 2000
      *
第 3 行出现错误:
ORA-00934: 此处不允许使用分组函数


SQL> ed
已写入 file afiedt.buf

  1  select deptno,avg(sum(nvl(sal,0))) 部门平均工资
  2  from emp
  3  where 部门平均工资 >= 2000
  4* group by deptno
SQL> /
where 部门平均工资 >= 2000
      *
第 3 行出现错误:
ORA-00904: "部门平均工资": 标识符无效


SQL> ed
已写入 file afiedt.buf

  1  select deptno,avg(sum(nvl(sal,0))) 部门平均工资
  2  from emp
  3  group by deptno
  4* having avg(sum(nvl(sal,0))) > 2000
SQL> /
having avg(sum(nvl(sal,0))) > 2000
           *
第 4 行出现错误:
ORA-00935: 分组函数的嵌套太深


SQL> ed
已写入 file afiedt.buf

  1  select deptno,avg(nvl(sal,0)) 部门平均工资
  2  from emp
  3  group by deptno
  4* having avg(nvl(sal,0)) > 2000
SQL> /

    DEPTNO 部门平均工资                                                                                                                                                                                 
---------- ------------                                                                                                                                                                                 
        20      2518.75                                                                                                                                                                                 
        10       4862.5                                                                                                                                                                                 

SQL> --having 跟where的作用一样,但是where不能跟分组函数
SQL> --查找10好部门的所有员工
SQL> select *
  2  from emp
  3  where deptno = 10
  4  group by deptno;
select *
       *
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式


SQL> --查找10好部门的最高工资
SQL> select empno,ename,max(sal) 最大的工资
  2  from emp
  3  where deptno = 10
  4  group by empno,ename;

     EMPNO ENAME      最大的工资                                                                                                                                                                        
---------- ---------- ----------                                                                                                                                                                        
      7782 CLARK            2450                                                                                                                                                                        
      7839 KING             5000                                                                                                                                                                        
      7777 lisi             2000                                                                                                                                                                        
      7934 MILLER          10000                                                                                                                                                                        

SQL> ed
已写入 file afiedt.buf

  1  select deptno,max(sal) 最大的工资
  2  from emp
  3  where deptno = 10
  4* group by deptno
SQL> /

    DEPTNO 最大的工资                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                   
        10      10000                                                                                                                                                                                   

SQL> ed
已写入 file afiedt.buf

  1  select deptno,max(sal) 最大的工资
  2  from emp
  3  group by deptno
  4* having deptno = 10
SQL> /

    DEPTNO 最大的工资                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                   
        10      10000                                                                                                                                                                                   

SQL> ed
已写入 file afiedt.buf

  1* select deptno,job,sum(sal) from emp group by deptno,job
SQL> /

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        50                                                                                                                                                                                              
        30 CLERK           1950                                                                                                                                                                         
        20 MANAGER         2975                                                                                                                                                                         
        30 SALESMAN        2750                                                                                                                                                                         
        20 CLERK           1100                                                                                                                                                                         
        10 PRESIDENT       5000                                                                                                                                                                         
        40                                                                                                                                                                                              
        30 MANAGER         2850                                                                                                                                                                         
        10 CLERK          10000                                                                                                                                                                         
        10 MANAGER         2450                                                                                                                                                                         
        20 ANALYST         6000                                                                                                                                                                         
        10 CODER           2000                                                                                                                                                                         

已选择12行。

SQL> ed
已写入 file afiedt.buf

  1* select deptno,job,sum(sal) from emp group by deptno,job order by deptno
SQL> /

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        10 CLERK          10000                                                                                                                                                                         
        10 CODER           2000                                                                                                                                                                         
        10 MANAGER         2450                                                                                                                                                                         
        10 PRESIDENT       5000                                                                                                                                                                         
        20 ANALYST         6000                                                                                                                                                                         
        20 CLERK           1100                                                                                                                                                                         
        20 MANAGER         2975                                                                                                                                                                         
        30 CLERK           1950                                                                                                                                                                         
        30 MANAGER         2850                                                                                                                                                                         
        30 SALESMAN        2750                                                                                                                                                                         
        40                                                                                                                                                                                              
        50                                                                                                                                                                                              

已选择12行。

SQL> ed
已写入 file afiedt.buf

  1* select deptno,sum(sal) from emp group by deptno
SQL> /

    DEPTNO   SUM(SAL)                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                   
        30       7550                                                                                                                                                                                   
        20      10075                                                                                                                                                                                   
        40                                                                                                                                                                                              
        50                                                                                                                                                                                              
        10      19450                                                                                                                                                                                   

SQL> ed
已写入 file afiedt.buf

  1* select deptno,job,sum(sal) from emp group by rollup(detpno,job)
SQL> /
select deptno,job,sum(sal) from emp group by rollup(detpno,job)
                                                    *
第 1 行出现错误:
ORA-00904: "DETPNO": 标识符无效


SQL> ed
已写入 file afiedt.buf

  1* select deptno,job,sum(sal) from emp group by rollup(deptno,job)
SQL> /

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        10 CLERK          10000                                                                                                                                                                         
        10 CODER           2000                                                                                                                                                                         
        10 MANAGER         2450                                                                                                                                                                         
        10 PRESIDENT       5000                                                                                                                                                                         
        10                19450                                                                                                                                                                         
        20 CLERK           1100                                                                                                                                                                         
        20 ANALYST         6000                                                                                                                                                                         
        20 MANAGER         2975                                                                                                                                                                         
        20                10075                                                                                                                                                                         
        30 CLERK           1950                                                                                                                                                                         
        30 MANAGER         2850                                                                                                                                                                         
        30 SALESMAN        2750                                                                                                                                                                         
        30                 7550                                                                                                                                                                         
        40                                                                                                                                                                                              
        40                                                                                                                                                                                              
        50                                                                                                                                                                                              
        50                                                                                                                                                                                              

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
                          37075                                                                                                                                                                         

已选择18行。

SQL> break on deptno skip 2;
SQL> /

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        10 CLERK          10000                                                                                                                                                                         
           CODER           2000                                                                                                                                                                         
           MANAGER         2450                                                                                                                                                                         
           PRESIDENT       5000                                                                                                                                                                         
                          19450                                                                                                                                                                         
                                                                                                                                                                                                        
                                                                                                                                                                                                        
        20 CLERK           1100                                                                                                                                                                         
           ANALYST         6000                                                                                                                                                                         
           MANAGER         2975                                                                                                                                                                         
                          10075                                                                                                                                                                         
                                                                                                                                                                                                        
                                                                                                                                                                                                        
        30 CLERK           1950                                                                                                                                                                         
           MANAGER         2850                                                                                                                                                                         
           SALESMAN        2750                                                                                                                                                                         
                           7550                                                                                                                                                                         

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
                                                                                                                                                                                                        
                                                                                                                                                                                                        
        40                                                                                                                                                                                              
                                                                                                                                                                                                        
                                                                                                                                                                                                        
                                                                                                                                                                                                        
        50                                                                                                                                                                                              
                                                                                                                                                                                                        
                                                                                                                                                                                                        
                                                                                                                                                                                                        
                          37075                                                                                                                                                                         
                                                                                                                                                                                                        
                                                                                                                                                                                                        

已选择18行。

SQL>
SQL>
SQL>
SQL>
SQL> --rollup 加强的group by
SQL> --break on xxx 当遇到xxx的时候只显示一次
SQL> --skip i 显示完成以后跳过i行
SQL> spool off


相关阅读:
Top