第六章分组函数

分组函数:

单行函数和分组函数的区别：

单行：输入一行，输出也是一行。处理一行

分组：输入可能多行（一组），输出只有一行。处理多行

• MIN函数和MAX函数 

      –MIN和MAX函数主要是返回每组的最小值和最大值。 

          •MIN([DISTINCT|ALL] column|expression) 

          •MAX([DISTINCT|ALL] column|expression) 

      –MIN和MAX可以用于任何数据类型 

      –查询入职日期最早和最晚的日期 

 

        SQL> SELECT         MIN(hiredate), MAX(hiredate) 

           2  FROM           emp; 

 

        MIN(HIRED MAX(HIRED 

        --------- --------- 

        17-DEC-80 12-JAN-83 

• SUM函数和AVG函数 

     –SUM和AVG函数分别返回每组的总和及平均值。 

         •SUM([DISTINCT|ALL] column|expression) 

         •AVG([DISTINCT|ALL] column|expression) 

     –SUM和AVG函数都是只能够对数值类型的列或表达式操作。 

     –查询职位以SALES开头的所有员工平均工资、最低工资、最 

       高工资、工资和。 

 

   SQL> SELECT       AVG(sal), MAX(sal), 

      2              MIN(sal), SUM(sal) 

      3  FROM        emp 

      4  WHERE        job LIKE 'SALES%'; 

 

   AVG(SAL)  MAX(SAL)  MIN(SAL)  SUM(SAL) 

   -------- --------- --------- --------- 

         1400         1600          1250         5600 

 

 

 COUNT函数 

    –COUNT函数的主要功能是返回满足条件的每组记录条数。 

        •COUNT( *|{[DISTINCT|ALL] column|expression}) 

 

      –COUNT(*)：*要用括号括起来，返回表中满足条件的行记录数(不去除空记录，其它都是去除空记录进行统计的)

      –查询部门30有多少个员工 

     SQL> SELECT      COUNT(*) 

        2  FROM       emp 

        3  WHERE      deptno = 30; 

 

       COUNT(*) 

      --------- 

               6 

 

 组函数中DISTINCT 

       –DISTINCT会消除重复记录后再使用组函数 

 

       –查询有员工的部门数量。 

 

     SQL> SELECT              COUNT(DISTINCT deptno) 

         2  FROM              emp; 

 

 

分组函数中空值处理 

     –除了COUNT （*）之外，其它所有分组函数都会忽略列中的空 

       值，然后再进行计算。 

 

     SQL> SELECT AVG(comm) 

       2  FROM      emp; 

 

      AVG(COMM) 

        --------- 

              550 

• 在分组函数中使用NVL函数 

        –NVL 函数可以使分组函数强制包含含有空值的记录 

 

     SQL> SELECT AVG(NVL(comm,0)) 

        2   FROM       emp; 

 

     AVG(NVL(COMM,0)) 

             ---------------- 

                    157.14286 

 

练习1 

 

• 1.查询部门20的员工，每个月的工资总和及平均 工资。 

SELECT  SUM (sal),AVG(sal) 
FROM  emp
GROUP BY deptno
HAVING deptno=20

• 2.查询工作在CHICAGO的员工人数，最高工资及最低工资。 

SELECT COUNT(ename),MAX(sal),MIN(sal)

FROM emp,dept

WHERE emp.deptno=dept.deptno

AND dept.loc='CHICAGO'

• 3.查询员工表中一共有几种岗位类型。 

SELECT COUNT (DISTINCT  job)

FROM emp

数据组：

使用 GROUP BY 子句 

 

  • 在SELECT列表中除了分组函数那些项（比如sum，avg等函数），所有列（只要select后边出现，那么group by中也必须出现）都必须包含在GROUP BY 子句中。 

 

  SQL> SELECT          deptno, AVG(sal) 

     2  FROM           emp 

     3  GROUP BY deptno; 

 

       DEPTNO     AVG(SAL) 

           --------- --------- 

                  10 2916.6667 

             20           2175 

                  30 1566.6667 

• GROUP BY 所指定的列并不是必须出现在SELECT 列表中。 

 

  SQL> SELECT           AVG(sal) 

      2   FROM          emp 

      3   GROUP BY deptno; 

 

    AVG(SAL) 

        --------- 

        2916.6667 

             2175 

        1566.6667 

 

 按多列分组的GROUP BY子句 ：

• 查询每个部门每个岗位的工资总和。 

 

     SQL> SELECT              deptno, job, sum(sal) 

         2  FROM              emp 

         3  GROUP BY deptno, job; 

 

          DEPTNO JOB                      SUM(SAL) 

                           --------- --------- --------- 

                  10 CLERK                          1300 

                  10 MANAGER                        2450 

                  10 PRESIDENT                      5000 

                 20 ANALYST                         6000 

                 20 CLERK                           1900 

      ... 

     9 rows selected. 

练习2 

 

• 1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。 

SELECT emp.deptno,dname,COUNT(empno),MAX(sal),MIN(sal),SUM(sal),AVG(sal)
FROM emp,dept
WHERE emp.deptno=dept.deptno(+)
GROUP BY emp.deptno,dname

• 2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资 ，工资总和，平均工资。 

SELECT e.deptno,d.dname,e.job,COUNT(e.empno),MAX(e.sal),MIN(e.sal),SUM(e.sal),
AVG(e.sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno,d.dname,e.job

• 3.查询每个经理所管理的人数，经理编号，经理 

   姓名，要求包括没有经理的人员信息。 

SELECT COUNT(e.empno ),m.empno,m.ename

FROM emp e,emp m

WHERE e.mgr=m.empno(+)

 

GROUP BY m.empno,m.ename

 

排除组结果：

 使用组函数的非法的查询 

 

• 不能在WHERE子句中限制组 

• 可以通过HAVING 子句限制组 

 

     SQL> SELECT            deptno, max(sal) 

         2  FROM            emp 

         3  WHERE           max(sal) > 2900 

         4   GROUP BY deptno; 

 

      WHERE AVG(sal) > 2000 

                * 

      ERROR at line 3: 

      ORA-00934: group function is not allowed here 

Select语句的书写顺序：

SELECT

FROM 

WHERE

GROUP BY

HAVING

ORDER BY

SELECT语句执行过程 

• SELECT语句执行过程： 

      –1.通过FROM子句中找到需要查询的表； 

      –2.通过WHERE子句进行非分组函数筛选判断； 

      –3.通过GROUP BY子句完成分组操作； 

      –4.通过HAVING子句完成组函数筛选判断； 

      –5.通过SELECT子句选择显示的列或表达式及组函数； 

      –6.通过ORDER BY子句进行排序操作。 

 

练习3 

 

• 1.查询部门人数大于2的部门编号，部门名称，部门人数。 

SELECT e.deptno,dname,COUNT(empno)

FROM emp e, dept d

WHERE e.deptno=d.deptno

GROUP BY e.deptno,dname

HAVING COUNT(empno)>2

• 2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部平均工资 ，并按照部门人数升序排序。 

SELECT e.deptno,d.dname,COUNT(empno),AVG(sal)
FROM emp e, dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno,d.dname
HAVING COUNT(empno)>2 AND AVG(sal)>2000
ORDER BY COUNT(empno)ASC

组函数的嵌套 

 • 显示平均薪水的最大值 

 

    SQL> SELECT          max(avg(sal)) 

       2  FROM           emp 

       3    GROUP BY deptno; 

 

    MAX(AVG(SAL)) 

           ------------- 

               2916.6667 

 

• 注：与单行函数不同，组函数只能嵌套两层 

课后作业 

 

• 1.查询部门平均工资在2500元以上的部门名称及平均工资。 

SELECT d.dname,AVG(sal)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY e.deptno,d.dname  

HAVING AVG(sal)>2500

• 2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

SELECT e.job,AVG(sal)

FROM emp e

WHERE e.job NOT LIKE 'SA%'

GROUP BY e.job

HAVING AVG(sal)>2500

ORDER BY AVG(sal) DESC 

• 3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

SELECT d.dname,round(MIN(sal),0),round(MAX(sal),0)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY e.deptno,d.dname

HAVING COUNT(empno)>2

 

• 4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

SELECT job,SUM(sal)

FROM emp

WHERE job<>'SALESMAN'

GROUP BY job 

HAVING SUM(sal)>2500 

• 5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。 

SELECT m.empno,m.ename,MIN(e.sal)

FROM emp e,emp m

WHERE e.mgr=m.empno(+)

GROUP BY e.mgr,m.empno,m.ename

HAVING MIN(e.sal)>=3000

ORDER BY MIN(e.sal) DESC

• 6.写一个查询，显示每个部门最高工资和最低工资的差额。 

SELECT MAX(sal)-MIN(sal)

FROM emp

GROUP BY deptno