Oracle数据库-第7章-分组统计查询

1、Oracle数据库第7章：分组统计查询统计函数No.组函数组函数描述描述1COUNT(* | DISTINCT 列)求出全部的记录数2SUM(列)求出总和，操作的列是数字3AVG(列)平均值4MAX(列)最大值5MIN(列)最小值6MEDIAN(列)返回中间值7VARIANCE(列)返回方差8STDDEV(列)返回标准差统计函数 范例n查找出公司每个月支出的月工资的总和SELECT SUM(sal) FROM emp ;统计函数 范例n查询出公司的最高工资、最低工资、平均工资SELECT AVG(sal), ROUND(AVG(sal),2), MAX(sal), MIN(sal) FROM

2、emp ;统计函数 范例n统计出公司最早雇佣和最晚雇佣的雇佣日期SELECT MIN(hiredate) 最早雇佣日期 , MAX(hiredate) 最晚雇佣日期 FROM emp ;统计函数 范例n统计公司工资之中中间的工资值SELECT MEDIAN(sal) FROM emp ;统计函数 范例n统计工资的标准差与方差SELECT STDDEV(sal),VARIANCE(sal) FROM emp ;统计函数 范例n统计出公司的雇员人数SELECT COUNT(empno) , COUNT(*) FROM emp ;统计函数 范例n 验证COUNT(*)、COUNT(字段)、COUNT

3、(DISTINCT 字段)的使用区别SELECT COUNT(*) , COUNT(ename) , COUNT(comm) , COUNT(DISTINCT job) FROM emp ;统计函数 范例n验证三种COUNT()函数的使用方式SELECT COUNT(ename) , AVG(sal) , SUM(sal) , MAX(sal) , MIN(sal) FROM bonus ;小结n五个核心的统计函数：COUNT()、AVG()、SUM()、MIN()、MAX()。什么时候需要分组？n对于分组这个概念在生活之中往往会听见以下的需要：需求一：需求一：在一个班级之中，要求男女各一组进

4、行辩论赛；需求二：需求二：在公司中，要求每个部门一组进行拔河比赛；针对于emp表数据分析nemp数据就可以根据不同的需要进行如下的分组：按照雇员的职位进行分组，job字段内容重复；按照雇员所在的部门分组，deptno字段内容重复；分组统计语法n SELECT DISTINCT 分组字段 AS 列别名 ,. | 统计函数 AS 别名 , .FROM 表名称1 表别名1 , 表名称2 表别名2 .WHERE 条件(s) GROUP BY 分组字段分组字段ORDER BY 排序字段 ASC|DESC ;分组统计 范例n统计出每个部门的人数SELECT deptno,COUNT(*)FROM empG

5、ROUP BY deptno ;分组统计 范例n统计出每种职位的最低和最高工资SELECT job , MIN(sal) , MAX(sal)FROM empGROUP BY job ;分组注意事项n 注意事项一：注意事项一：如果没有GROUP BY子句，则在SELECT子句之中只允许出现统计函数，其他任何字段都不允许出现。错误的范例：错误的范例： SELECT deptno , COUNT(empno) FROM emp ;正确的范例：正确的范例： SELECT COUNT(empno) FROM emp ;n 注意事项二：注意事项二：在统计查询之中，SELECT子句后只允许出现分组字段和统

6、计函数，而其他的非分组字段不能使用。错误的范例：错误的范例： SELECT deptno,ename,COUNT(empno) FROM emp GROUP BY deptno ;正确的范例：正确的范例： SELECT deptno, COUNT(empno) FROM emp GROUP BY deptno ;n 注意事项三：注意事项三：统计函数允许嵌套使用，但是嵌套统计函数之后的SELECT子句之中不允许再出现任何的字段，包括分组字段。正确的范例：正确的范例：SELECT MAX(AVG(sal) FROM emp GROUP BY deptno ;错误的范例：错误的范例： SELECT

7、deptno,MAX(AVG(sal) FROM emp GROUP BY deptno ;分组统计 范例n 查询每个部门的名称、部门人数、部门平均工资、平均服务年限SELECT d.dname, COUNT(e.empno) , ROUND(AVG(e.sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate) / 12),2) avgyearFROM dept d,emp e WHERE e.deptno(+)=d.deptnoGROUP BY d.dname;分组统计 范例n 查询出公司各个工资等级雇员的数量、平均工资。SEL

8、ECT s.grade,COUNT(e.empno), ROUND(AVG(e.sal),2) FROM emp e,salgrade sWHERE e.sal BETWEEN s.losal AND s.hisal GROUP BY s.grade ;分组统计 范例n 统计出领取佣金与不领取佣金的雇员的平均工资、平均雇佣年限、雇员人数。SELECT 不领取佣金, ROUND(AVG(sal),2) avgsal,ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,COUNT(empno) count FROM emp WHERE

9、 comm IS NOT NULL UNIONSELECT 领取佣金, ROUND(AVG(sal),2) avgsal,ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2)avgyear,COUNT(empno) count FROM emp WHERE comm IS NULL ;小结n利用GROUP BY可以设置分组；nSELECT子句在分组操作中的限制。多字段分组统计n SELECT DISTINCT 分组字段1 AS 列别名 , 分组字段2 AS 列别名 , | 统计函数 AS 别名 , .FROM 表名称1 表别名1 , 表名称2 表别

10、名2 .WHERE 条件(s) GROUP BY 分组字段分组字段1 , 分组字段分组字段2 , .ORDER BY 排序字段 ASC|DESC ;分组统计 范例n现在要求查询出每个部门的详细信息。SELECT d.deptno,d.dname,d.loc,NVL(COUNT(e.empno),0) count,NVL(ROUND(AVG(sal),2),0) avg, NVL(SUM(sal),0) sum,NVL(MAX(sal),0) max,NVL(MIN(sal),0) minFROM emp e,dept dWHERE e.deptno(+)=d.deptnoGROUP BY d.

11、deptno,d.dname,d.loc ;小结n多字段分组是GROUP BY定义多个字段，且多个字段必须同时重复。HAVING子句n 使用GROUP BY子句可以实现数据的分组显示，但是在很多时候往往需要对分组之后的数据进行再次的过滤，例如：要求选出部门人数超过5个人的部门信息，这样的操作肯定是先要按照部门进行分组统计，而后再通过统计结果进行数据的过滤，而要想实现这样的功能就只能通过HAVING子句完成。SELECT DISTINCT 分组字段1 AS 列别名 , 分组字段2 AS 列别名 , | 统计函数 AS 别名 , .FROM 表名称1 表别名1 , 表名称2 表别名2 .WHERE

12、 条件(s) GROUP BY 分组字段1 , 分组字段2 , .HAVING 过滤条件过滤条件(s)ORDER BY 排序字段 ASC|DESC ;分组统计 范例n查询出所有平均工资大于2000的职位信息、平均工资、雇员人数SELECT job, ROUND(AVG(sal),2) , COUNT(empno) FROM emp GROUP BY job HAVING AVG(sal)2000 ;分组统计 范例n 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。SELECT d.deptno,d.dname,ROUND(AVG(e.sal),2),MIN(e.sal),MAX(e.sal)FROM emp e,dept dWHERE e.deptno(+)=d.deptnoGROUP BY d.deptno,d.dname,d.locHAVING COUNT(e.empno)1;语句执行流程分组统计 范例n 显示非销售人员工作名称以及从事