SQL统计函数及分组操作

本文链接：https://blog.csdn.net/dingchenxixi/article/details/70193634

1.多表查询的操作、限制、笛卡尔积的问题 
2.统计函数及分组统计的操作 
3.子查询的操作，并且结合限定查询、数据排序、多表查询、统计查询一起完成各个复杂查询的操作 
4.数据库的更新操作：增加、修改、删除数据 
5.数据伪列的作用：ROWNUM,ROWID； 
6.完成一套复杂查询的应用案例

统计函数及分组统计的操作
统计函数

统计函数主要有： 
COUNT():用于统计数据数量 
SUM():用于统计数据和 
AVG()：统计平均值 
MIN()：得到最小值 
MAX()：得到最大值 
测试COUNT(),SUM(),AVG()函数，求出所有员工的总工资，平均工资：

• 1 select count(empno),sum(sal),avg(sal) from emp;

 

测试MIN(),MAX()，求出员工中最大工资和最小工资：

 

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

• 

注意关于COUNT()函数，当数据表中没有数据时，COUNT()总是会返回一个数值，只是这个数值是“0” 
关注BONUS数据表：

分组统计

当数组重复的时候分组才有意义，因为一个人也可以分为一组，只是没有意义而已，分组采用GROUP BY语句完成，语法如下：
1 SELECT [DISTINCT] * | 列名称 [AS]别名,……..
2 FROM 表名称1 [别名1]
3 [WHERE 条件(s)]
4 [GROUP BY 分组字段1[,分组字段2,...]]
5 [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完成：
1 select d.deptno,d.dname,d.loc,nvl(avg(e.sal),0) from dept d, emp e 
2 where d.deptno=e.deptno(+) and nvl(avg(e.sal),0)>2000
3 group by d.deptno,d.dname,d.loc;

本错误提示的核心意思在于where子句中不能使用统计函数，之所以在where子句中不能使用，实际上跟where子句的主要功能有关，where的主要功能是从全部的数据之中取出部门数据，而这种分组完后需要筛选的就需要用到‘HAVING’子句：
1 select d.deptno,d.dname,d.loc,nvl(avg(e.sal),0) from dept d, emp e 
2 where d.deptno=e.deptno(+) 
3 group by d.deptno,d.dname,d.loc 
4 having nvl(avg(e.sal),0)>2000;

 

WHERE和HAVING的别 
WHERE：是在执行GROUP BY操作之前进行的过滤，表示从全部数据中筛选出部门数据，WHERE之中不能使用统计函数 
HAVING:是在GROUP BY分组之后的再次过滤，可在HAVING子句中使用统计函数

一道综合题： 
显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资的合计升序排列 
第一步：考虑非销售人员的情况

1 select * from emp where job<>'SALESMAN';
第二步：统计从事同一工作雇员的月工资总和

1 select job ,sum(sal) sum from emp where job<>'SALESMAN' group by job;
第三步：满足从事同一工作的雇员的月工资合计大于5000：

1 select job ,sum(sal) sum from emp where job<>'SALESMAN' group by job having sum(sal)>5000
第四步：输出结果按月工资的合计升序排列：

1 select job ,sum(sal) sum from emp where job<>'SALESMAN' group by job having sum(sal)>5000 order by  sum asc;