一、统计函数
|
No.
|
组函数
|
描述
|
|
1
|
count(*|[distinct]列)
|
求出全部的记录数
|
|
2
|
sum(列)
|
求出总和，操作的列是数字
|
|
3
|
avg(列)
|
平均值
|
|
4
|
max(列)
|
最大值
|
|
5
|
min(列)
|
最小值
|
|
6
|
median(列)
|
返回中间值
|
|
7
|
variance(列)
|
返回方差
|
|
8
|
stddev(列)
|
返回标准差
|
实际上只有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 emp
group by deptno;
范例：统计出每种职位的最低和最高工资
select job ,min(sal),max(sal)
from emp
group 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 avgyear
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.dname;
子句的执行顺序：from,where,group by,select,order by.
范例：查询出公司各个工资等级雇员的数量、平均工资。
步骤一：分析需要的表emp e,salgrade s
步骤二：关联条件 where e.sal between s.losal and s.hisal
select s.grade,count(e.empno),round(avg(e.sal),2)
from emp e,salgrade s
where e.sal between s.losal and s.hisal
group by s.grade
order by s.grade asc;
三、多字段分组统计：
在进行单子段分组的时候不管是单表取数据，还是多表查询，那么在一个列上一定会存在重复记录。
多字段分组，本质上没有区别，也就是若干个列上的数据同时存在重复。
多字段分组统计语法
select[distinct]分组字段1，分组字段2
from 表名1，表名2
where 条件
group by 分组字段1，分组字段2
order 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 d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;
四、HAVING子句：
使用group by 子句可以实现数据的分组显示，但是很多时候往往需要对分组之后的数据再次进行过滤
例如：要求选出部门人数超过五个的部门信息，这样的操作肯定是先要按照部门进行分组统计，而后
再通过统计结果进行数据的过滤，而要想实现这样的功能就只能通过HAVING子句完成。
having子句的语法：
select 分组字段1，分组字段2
from 表名称1，表名称2
where 条件
group by 分组字段1，分组字段2
having 过滤条件
order by 排序字段 asc|desc;
注意：having子句一定要跟group by子句一起使用，先后顺序没有具体要求。
范例：查询出所有平均工资大于2000的职位信息、平均工资、雇员人数。
select job,round(avg(sal),2),count(empno)
from emp
group by job
having 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 d
where e.deptno=d.deptno
group by d.deptno,d.dname;
对于having和where子句区别的描述：
where:是在分组之前使用，不允许使用统计函数
having：是在分组之后使用（必须包含group by），允许使用统计函数
范例：
显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且要满足从事同一工作的雇员的
月工资合计大于5000，输出结果按月工资的升序排列。
select job,
sum(sal) sum
from emp
where job<>'SALESMAN'
group by job
having sum(sal)>5000
order by sum asc
