SQL统计函数及分组操作 | 您所在的位置:网站首页 › SQL计算客户年龄 › SQL统计函数及分组操作 |
1.多表查询的操作、限制、笛卡尔积的问题 2.统计函数及分组统计的操作 3.子查询的操作,并且结合限定查询、数据排序、多表查询、统计查询一起完成各个复杂查询的操作 4.数据库的更新操作:增加、修改、删除数据 5.数据伪列的作用:ROWNUM,ROWID; 6.完成一套复杂查询的应用案例 统计函数及分组统计的操作统计函数 统计函数主要有: COUNT():用于统计数据数量 SUM():用于统计数据和 AVG():统计平均值 MIN():得到最小值 MAX():得到最大值 测试COUNT(),SUM(),AVG()函数,求出所有员工的总工资,平均工资: select count(empno),sum(sal),avg(sal) from emp; 测试MIN(),MAX(),求出员工中最大工资和最小工资: select min(sal),max(sal) from emp; 注意关于COUNT()函数,当数据表中没有数据时,COUNT()总是会返回一个数值,只是这个数值是“0” 关注BONUS数据表:分组统计 当数组重复的时候分组才有意义,因为一个人也可以分为一组,只是没有意义而已,分组采用GROUP BY语句完成,语法如下: SELECT [DISTINCT] * | 列名称 [AS]别名,…….. FROM 表名称1 [别名1] [WHERE 条件(s)] [GROUP BY 分组字段1[,分组字段2,...]] [ORDER BY 排序的字段 1,ASC| DESC,排序的字段2 ASC| DESC,….]例子: 按照部门编号分组,求出每个部门的人数,平均工资 select count(empno),avg(sal) from emp; 按照职位分组,求出每个职位的最高和最低工资: select job,max(sal),min(sal) from emp group by job; 一旦分组之后,实际上对于语法上就会出现新的限制: 分组函数可在 没有分组的时候单独使用,却 不能出现其他的查询字段:ename就是其它查询字段。在select子句之后,只能出现分组的字段和统计函数,其它的字段不能出现 分组函数允许嵌套,但是嵌套之后的分组函数的查询之中不能再出现任何其它字段 例子:按照职位分组,统计平均工资最高的工资 select max(avg(sal)) from emp group by job; 当添加其它字段‘job’之后出现错误例子:查询出每个部门的名称、位置、部门的人数、平均工资 确定所需的数据表: emp表:部门的人数,平均工资 dept表:部门的名称,位置 确定已知的关联字段: emp.deptno = dept.deptno 发现dname存在重复,可以进行分组去除重复,按照之前对分组的理解,只要数据重复那么就有可能进行分组的查询操作,但是此时与之前的分组操作 不太一样,之前的分组是针对一张实体表进行分组的(emp,dept都属于实体表),但是对于以上的数据是通过查询结果显示的,所以是一张临时的虚拟表,但是不管是否是实体表还是虚拟表,只要是有重复,那么就直接进行分组 select d.dname,d.loc,count(e.empno),avg(e.sal) from emp e ,dept d where e.deptno=d.deptno group by d.dname,d.loc;但是上面这个并不完美,因为dept表中一共有4个部门,那么改善一下,加个连接,同时考虑到部门没有人的话,那么工资肯定是NULL的,所以需要使用到NVL()函数: select d.dname,d.loc,count(e.empno),nvl(avg(e.sal),0) from emp e ,dept d where e.deptno(+)=d.deptno group by d.dname,d.loc;或许刚开始你会写成这样: 记住 一定是分组字段和统计函数 例子:要求统计出每个部门的详细信息,并且要求这些部门的平均工资高于2000 在以上程序的基础之上,就目前而言,需要加上限定语句where子句,所以考虑用where完成: select d.deptno,d.dname,d.loc,nvl(avg(e.sal),0) from dept d, emp e where d.deptno=e.deptno(+) and nvl(avg(e.sal),0)>2000 group by d.deptno,d.dname,d.loc; 本错误提示的核心意思在于where子句中不能使用统计函数,之所以在where子句中不能使用,实际上跟where子句的主要功能有关,where的主要功能是从全部的数据之中取出部门数据,而这种分组完后需要筛选的就需要用到‘HAVING’子句: select d.deptno,d.dname,d.loc,nvl(avg(e.sal),0) from dept d, emp e where d.deptno=e.deptno(+) group by d.deptno,d.dname,d.loc having nvl(avg(e.sal),0)>2000;WHERE和HAVING的别 WHERE:是在执行GROUP BY操作之前进行的过滤,表示从全部数据中筛选出部门数据,WHERE之中不能使用统计函数 HAVING:是在GROUP BY分组之后的再次过滤,可在HAVING子句中使用统计函数 一道综合题: 显示非销售人员工作名称以及从事同一工作雇员的月工资总和,并且要满足从事同一工作的雇员的月工资合计大于5000,输出结果按月工资的合计升序排列 第一步:考虑非销售人员的情况 select * from emp where job'SALESMAN';第二步:统计从事同一工作雇员的月工资总和 select job ,sum(sal) sum from emp where job'SALESMAN' group by job;第三步:满足从事同一工作的雇员的月工资合计大于5000: select job ,sum(sal) sum from emp where job'SALESMAN' group by job having sum(sal)>5000第四步:输出结果按月工资的合计升序排列: select job ,sum(sal) sum from emp where job'SALESMAN' group by job having sum(sal)>5000 order by sum asc; |
CopyRight 2018-2019 实验室设备网 版权所有 |