Oracle学习4-分组统计查询

一、统计函数No.组函数   描述1count(*|[distinct]列)求出全部的记录数2sum(列)求出总和，操作的列是数字3avg(列)平均值4max(列)最大值5min(列)最小值6median(列)返回中间值7variance(列)返回方差8stddev(列)返回标准差实际上只有5个标准函数：count(),sum(),avg(),max(),min()sum()函数范例：查出公司每个月的月工资总和：select sum(sal) from emp;范例：查询公司最高工资、最低工资、平均工资select max(sal),min(sal),round(avg(sal),2) from emp;范例：统计出公司最早雇佣和最晚雇佣的雇佣日期select min(hiredate),max(hiredate) from emp;范例：统计公司工资之中中间的工资值：select median(sal)from emp;范例：验证count(*),count(字段),count(distinc 字段)的使用区别select count(*),count(ename),count(comm),count(distinct job)from emp;对于count函数而言，可以传递三类内容：*、字段、distinct字段在使用count（字段）的时候，如果列上存在了null，那么null是不会进行统计的。如果使用的distinct列上的重复也不进行统计。使用count(*)是最方便的，不过建议用count(字段)二、单字段分组：分组统计语法：使用group by 子句select [distinct]分组字段，from 表名称1[表别名1]，表名称2[表别名2]，where 条件[group by 分组字段]order by 排序字段 asc|desc;范例：统计出每个部门的人数select deptno ,count(deptno) from empgroup by deptno;范例：统计出每种职位的最低和最高工资select job ,min(sal),max(sal)from empgroup by job;分组注意事项：1.如果没有group by 子句，则在select 子句之中只允许出现统计函数，其他任何字段都不允许出现。  错误范例：select deptno,count(empno) from emp;  正确范例：select count(empno) from emp;2.在统计查询之中，select 子句后只允许出现分组字段和统计函数，而其他的非分组字段不能使用。  错误范例：select deptno,ename,count(empno) from emp group by deptno;  正确范例：select deptno,count(ename),count(empno)from emp group by deptno;3.统计函数允许嵌套使用，但是嵌套统计函数之后的select子句之中不允许再出现任何的字段，包括分组字段。  错误范例：select deptno,max(avg(sal)) from emp group by deptno;  正确范例：select max(avg(sal)) from emp group by deptno;范例：查询每个部门的名称、部门人数、部门平均工资、平均服务年限select d.dname,count(e.deptno),round(avg(e.sal),2),round(avg(months_between(sysdate,e.hiredate)/12),1) as avgyearfrom emp e,dept dwhere e.deptno(+)=d.deptnogroup by d.dname;子句的执行顺序：from,where,group by,select,order by.范例：查询出公司各个工资等级雇员的数量、平均工资。步骤一：分析需要的表emp e,salgrade s步骤二：关联条件 where e.sal between s.losal and s.hisalselect s.grade,count(e.empno),round(avg(e.sal),2)from emp e,salgrade swhere e.sal between s.losal and s.hisalgroup by s.gradeorder by s.grade asc;三、多字段分组统计：在进行单子段分组的时候不管是单表取数据，还是多表查询，那么在一个列上一定会存在重复记录。多字段分组，本质上没有区别，也就是若干个列上的数据同时存在重复。多字段分组统计语法select[distinct]分组字段1，分组字段2from 表名1，表名2where 条件group by 分组字段1，分组字段2order by 排序字段 asc|desc范例：要求查询出每个部门的详细信息：部门编号，部门名称，位置，平均工资、总工资，最高工资，最低工资，部门人数。select d.deptno,d.dname,d.loc,count(e.empno),nvl(round(avg(e.sal),2),0),nvl(sum(e.sal),0),nvl(max(e.sal),0),nvl(min(e.sal),0)from emp e,dept dwhere e.deptno(+)=d.deptnogroup by d.deptno,d.dname,d.loc;四、HAVING子句：使用group by 子句可以实现数据的分组显示，但是很多时候往往需要对分组之后的数据再次进行过滤例如：要求选出部门人数超过五个的部门信息，这样的操作肯定是先要按照部门进行分组统计，而后再通过统计结果进行数据的过滤，而要想实现这样的功能就只能通过HAVING子句完成。having子句的语法：select 分组字段1，分组字段2 from 表名称1，表名称2where 条件group by 分组字段1，分组字段2having 过滤条件order by 排序字段 asc|desc;注意：having子句一定要跟group by子句一起使用，先后顺序没有具体要求。范例：查询出所有平均工资大于2000的职位信息、平均工资、雇员人数。select  job,round(avg(sal),2),count(empno)from empgroup by jobhaving round(avg(sal),2)>2000完整的子句执行顺序： from ,where ,group by,having,select,order by范例：列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资，最低工资，最高工资select d.deptno,d.dname,round(avg(e.sal)),max(e.sal),min(e.sal)from emp e,dept dwhere e.deptno=d.deptnogroup by d.deptno,d.dname;对于having和where子句区别的描述：where:是在分组之前使用，不允许使用统计函数having：是在分组之后使用（必须包含group by），允许使用统计函数范例：显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资的升序排列。select job,sum(sal) sumfrom emp where job'SALESMAN'group by jobhaving sum(sal)>5000order by sum asc