Oracle使用分组函数汇总数据
- 1.1 【常用分组函数类型】
- 1.2 【分组函数基本语法】
- 1.3 【使用 avg、 max、 min、 sum 函数】
- 1.4 【使用 COUNT 函数】
- 1.4.1 count(distinct)
- 1.5 【分组函数和空值】
- 1.6【group by 创建聚组数据】
- 1.6.1 【group by 子句基本语法】
- 1.7 【使用 group by 子句】
- 1.8 【根据多个列进行分组】
- 1.9 【分组函数的误用】
- 1.10 【having 排除聚组结果】
- 1.11 【分组函数的嵌套使用】
- 【总结】
1.1 【常用分组函数类型】
1.2 【分组函数基本语法】
1.3 【使用 avg、 max、 min、 sum 函数】
需求：查询 **emp **表销售人员工资的平均值、最大值、最小值、工资总和
-- 查询 emp 表销售人员工资的平均值、最大值、最小值、工资总和SCOTT@orcl>select avg(sal),max(sal),min(sal),sum(sal) from emp;
日期类型、字符类型列不能使用 avg、 **sum **函数，可以使用 max、 **min **函数
1.4 【使用 COUNT 函数】
需求： 查询 **30 **号部门的总人数
-- 查询 30 号部门的总人数SCOTT@orcl>select * from emp where deptno=30;SCOTT@orcl>select count(*) from emp where deptno=30;
SCOTT@orcl>select count(empno) from emp where deptno=30;SCOTT@orcl>select count(comm) from emp where deptno=30;
【说明】count(comm)： 不统计含有空值的行；COUNT(*)返回表中行的总数， 包括重复行与数据列中含有空值的行， 而其他分组函数的统计都不
包括空值的行。
1.4.1 count(distinct)
SCOTT@orcl>select distinct mgr from emp;SCOTT@orcl>select count(distinct mgr) from emp;
1.5 【分组函数和空值】
SCOTT@orcl>select count(comm) from emp;
需求：查询所有 **14 **名员工的平均奖金
-- 查询所有 14 名员工的平均奖金SCOTT@orcl>select avg(comm) from emp;
SCOTT@orcl>select avg(nvl(comm,0)) from emp;
1.6【group by 创建聚组数据】
计算每个部门的平均工资,需要指定分组的列。
1.6.1 【group by 子句基本语法】
1.7 【使用 group by 子句】
SCOTT@orcl>select avg(sal) from emp;SCOTT@orcl>select deptno,avg(sal) from emp group by deptno;
SCOTT@orcl>select deptno,avg(sal) from emp;
**select **语句中，没有使用分组函数的列必须在 **group by **子句中
SCOTT@orcl>select avg(sal) from emp group by deptno;
**group by **后面的列可以不出现在 **select **语句中
SCOTT@orcl>select deptno dnum,avg(sal) from emp group by dnum;
**group by **后面不允许使用列的别名
1.8 【根据多个列进行分组】
需求：查询每个部门中，每种工作的工资总和。
-- 查询每个部门中，每种工作的工资总和。SCOTT@orcl>select deptno,job,sum(sal) from emp2group by deptno,job order by deptno;
1.9 【分组函数的误用】
SCOTT@orcl>select deptno,count(ename) from emp;
**select **语句中有分组函数，没有在分组函数中出现的列必须在 **group by **子句中
SCOTT@orcl>select deptno,avg(sal) from emp2where avg(sal) >20003group by deptno;
不能在 **where **子句中对聚组列作出限定，应使用 **having **子句
1.10 【having 排除聚组结果】
SCOTT@orcl>select deptno,avg(sal) from emp2having avg(sal)>20003group by deptno;
需求：查询部门最高工资大于 **2900 **的部门
-- 查询部门最高工资大于 2900 的部门SCOTT@orcl>select deptno,max(sal) from emp2group by deptno3having max(sal)>2900;SCOTT@orcl>select deptno,max(sal) from emp2having max(sal)>29003group by deptno;
**having **子句可以放在 **group by **子句前面。
1.11 【分组函数的嵌套使用】
SCOTT@orcl>select max(avg(sal)) from emp group by deptno;
分组函数最多嵌套两层**(两个函数)**，嵌套时必须有 group by
SCOTT@orcl>select count(max(avg(sal))) from emp group by deptno;
嵌套三层失败
【总结】
分组函数： avg/max/min/sum/count
分组函数（聚合函数）要处理空值；select 子句中没有被分组函数包括的列，必须出现在 group by 后面；group by 子句中的列可以不出现在 select 语句中；group by 子句中不能使用列的别名；
如果对分组函数做限制，不能用 where,需要用 having，having 子句可以写在 group by 子句前；
分组函数最多嵌套两层（两个函数）。