SQL语句-查询语句-组函数

组函数语法

对多行进行的计算

组函数类型 解释
avg() 平均
count() 统计
max() 最大值
min() 最小值
stddev 估算标准偏差
sum 求和
variance 方差
  • group by 字句进行分组
  • having 字句聚合函数过滤
序列 举例
rownum() 1 2 3 4 5
rank() over (partition by order by) 1 2 2 4 5
dense_rank() over (partition by order by) 1 2 2 3 4 
select rank() over (partition by deptno order by sal desc) ord from emp;
  • partition by 给结果集分组
  • order by 给结果集排序
  • rank() 在每个分组内部进行排名

应用实例

1. 雇员中最大工资，最小工资，工资总和，平均工资。

SQL> select min(sal),max(sal),sum(sal),avg(sal) from emp;

  MIN(SAL)   MAX(SAL)	SUM(SAL)   AVG(SAL)
---------- ---------- ---------- ----------
       800	 5000	   29025 2073.21429

2. 每个部门的工资总和和平均值

SQL> select deptno,to_char(avg(sal),'L99999.99') avg_sal,to_char(sum(sal),'L99999.99') sum_sal from emp group by deptno;

    DEPTNO AVG_SAL	       SUM_SAL
---------- ------------------- -------------------
	30	      $1566.67		  $9400.00
	20	      $2175.00		 $10875.00
	10	      $2916.67		  $8750.00

3. 部门工资总和的最大值为多少

SQL> select max(sum(sal)) from emp group by deptno;

MAX(SUM(SAL))
-------------
	10875

4. 部门工资总和最多的部门名称和工资总和

SQL> select deptno,sum_sal from (select deptno,sum(sal) sum_sal from emp group by deptno order by sum_sal desc ) where rownum < 2;

    DEPTNO    SUM_SAL
---------- ----------
	20	10875

5. 工资总和超过9000的部门

SQL> select deptno,sum(sal) sum_sal from emp group by deptno having sum_sal > 9000;
select deptno,sum(sal) sum_sal from emp group by deptno having sum_sal > 9000
                                                               *
ERROR at line 1:
ORA-00904: "SUM_SAL": invalid identifier


SQL> select deptno,sum(sal) sum_sal from emp group by deptno having sum(sal) > 9000;

    DEPTNO    SUM_SAL
---------- ----------
	30	 9400
	20	10875

  • 注意having字句后面不可以使用别名。
  • where字句不可以过滤组函数运算后的结果。

6. 雇员中工资相同的员工分别是谁，工资为所少？

SQL> select e1.ename,e2.ename,e1.sal from emp e1,emp e2 where e1.sal=e2.sal and e1.ename != e2.ename;

ENAME	   ENAME	     SAL
---------- ---------- ----------
MARTIN	   WARD 	    1250
WARD	   MARTIN	    1250
FORD	   SCOTT	    3000
SCOTT	   FORD 	    3000

7. 相同的工资有几个？

SQL> select sal,count(sal) from emp group by sal;

       SAL COUNT(SAL)
---------- ----------
      2450	    1
      5000	    1
      1300	    1
      1250	    2
      2850	    1
      2975	    1
      1100	    1
      3000	    2
       800	    1
      1600	    1
      1500	    1

       SAL COUNT(SAL)
---------- ----------
       950	    1

12 rows selected.

SQL> select sal,count(sal) from emp group by sal having count(sal)>1;

       SAL COUNT(SAL)
---------- ----------
      1250	    2
      3000	    2

SQL> select sal from emp group by sal having count(sal)>1;

       SAL
----------
      1250
      3000

SQL> select ename,sal from emp where sal in (select sal from emp group by sal having count(sal)>1);

ENAME		  SAL
---------- ----------
WARD		 1250
MARTIN		 1250
SCOTT		 3000
FORD		 3000

8. 每一年参加工作的雇员的数量

SQL> select count(to_char(hiredate,'yyyy')) enum, to_char(hiredate,'yyyy') year from emp group by to_char(hiredate,'yyyy');

      ENUM YEAR
---------- ----
	 2 1987
	 1 1980
	 1 1982
	10 1981

SQL> select sum(case when to_char(hiredate,'yy')='81' then 1 else 0 end) "81",sum(decode(to_char(hiredate,'yy'),82,1,0)) "82",sum(decode(to_char(hiredate,'yy'),80,1,0)) "80",sum(decode(to_char(hiredate,'yy'),87,1,0)) "87" from emp;

	81	   82	      80	 87
---------- ---------- ---------- ----------
	10	    1	       1	  2


课后练习

select max(sal),min(sal),sum(sal),avg(sal) from emp;

select count(*) from emp;
select count(*) from emp where deptno=30;
select count(deptno) from emp;
select count(distinct deptno) from emp;
select count(comm) from emp;
select avg(comm) from emp;
select avg(nvl(comm,0)) from emp;
select deptno,sum(sal) from emp group by deptno;
select deptno,job,sum(sal) from emp group by deptno,job;
select deptno,sum(sal) from emp having sum(sal)>9000 group by deptno;
--------------------------------
10部门的最大工资
--------------------------------
查找重复的工资
--------------------------------
80年 81年 82年 87年都有多少新员工

1980  1981  1982  1987
----  ----  ----  ----
   1    10     1     2
--------------------------------