| 第六章：oracle分组函数(多行函数) | 您所在的位置：网站首页 › oracle分组函数最多可以嵌套几层 › 第六章：oracle分组函数(多行函数) |
|
---------------------------------------------------------
--分组函数：对多行数据进行操作
请思考如下问题？
查询所有员工的每个月工资总和，平均工资？
查询工资最高和最低的工资是多少？
查询公司的总人数？
查询有奖金的总人数？
五个常用的分组函数：
min:求最小值
max:求最大值
sum:求和
avg:求平均值
count:统计
---------------------------------------------------------
--min和max
注意：
MIN和MAX可以用于任何数据类型
查询工资最高和最低的工资是多少？
select min(sal),max(sal) from emp;--数值类型
查询入职日期最早和最晚的日期
select min(hiredate),max(hiredate) from emp;-- 日期类型
select min(ename),max(ename) from emp; -- 字符类型
--SUM函数和AVG函数
注意：
1、SUM和AVF函数都只是能够对数值类型的列或表达式操作
2、avg函数会忽略空值，所以一般需要排除空值的影响
查询所有员工的每个月工资总和，平均工资？
select sum(sal) from emp;--229023
select avg(sal) from emp;--14313.9375
查询所有员工的平均奖金、奖金总和
select sum(comm) from emp;--91088
--91088/16=5693
select avg(comm) from emp;--18217.6 91088/5
select avg(nul(comm,o)) from emp;--5693 avg函数会忽略控制字，所以一般需要排除空值的影响
查询职位以SALES开头的所有员工平均工资、最低工资、最高工资、工资和
select avg(nvl(sal,0)) 平均工资，min(sal) 最低工资,max(sal) 最高工资, sum(sal) 工资和
from emp where job like 'SALES%';
--COUNT函数 COUNT函数的主要功能是返回满足条件的每组记录条数
注意：count(*)不会受空值的影响
count(列名)：会受到空值的影响：忽略null
查询公司人数
select * from emp;
select count(*) from emp;--16 速度慢
select empno from emp;
select count(empno) from emp;-- 速度较快
select comm from emp;
select count(comm) from emp;--5
select nul(comm,123123) from emp;--123123只是占位符
select count(nvl(comm,123123)) from emp;--16
select 1 from emp;--1只是占位符
select count(1) from emp;--16 速度快
查询30号部分有多少员工
select count(1) from emp where deptno=30;
查询部门30有多少个员工领奖金
select comm from emp where deptno=30;--14
select count(comm) from emp where deptno=30;--4
查询有员工的部门数量
select count(deptno) from emp;--15
select count(distinct deptno) from emp;--3 distinct:排除重复
---------------------group by子句------------------------
注意：
1、可以使用group by 子句先对满足where条件的数据行进行分组，再求分组后的数据
2、其中GROUP BY 子句指定要分组的列 group by 列名 不能使用列别名
3、在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY 子句中
4.GROUP BY 所指定的并不是必须出现在SELECT 列表中
5、按多列分组时，列是有顺序的 例如：先对部门分组再对岗位进行分组
-group by deptno ,job
--按照单列分组
查询每个部门的平均工资?
select avg(sal) from emp where deptno=10
select avg(sal) ,deptno from emp group by deptno
查询每个部门的编号，平均工资
select avg(sal),deptno from emp group by deptno
select avg(sal) from emp group by deptno;
--按多个列分组
查询每个部门每个岗位的工资总和
--每个部门的工资总和
select sum(sal),deptno from emp group by deptno;
思路：先对部门分组再对岗位进行分组
select sum(sal),deptno,job
from emp
group by deptno,job
每个部门的人数
select count(1),deptno from emp group by deptno
–1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。没有人数的部门也显示 select d.deptno,dname,count(1),max(sal),min(sal),sum(sal),avg(sal) from emp e ,deptno d where e.deptno(+)=d.deptno group by d.deptno,dname –2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。 select d.deptno,dname,job,count(1),max(sal),min(sal),sum(sal),avg(sal) from emp e,dept d where e.deptno=d.deptno group by d.deptno,dname,job –3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有下属的经理信息。 select count(1),m.empno,m.ename from emp w,emp m where w.mgr(+)=m.empno group by m.empno,m.ename select m.empno,m.ename 经理,count(w.empno) from emp w,emp m where w.mgr(+)=m.empno group by m.empno,m.ename –3.查询每个经理所管理的人数，经理编号，经理姓名，没有经理的员工也显示 select m.empno,m.ename 经理,count(w.empno) from emp w,emp m where w.mgr=m.empno(+) group by m.empno,m.ename ---------------------having子句------------------------ 注意： 1、对分组之后的数据进行再一次筛选 2、建议having子句要跟在group by子句之后 查询每个部门最高工资大于2900的部门编号，最高工资 select deptno,max(sal) from emp group by deptno having 2900 |
| CopyRight 2018-2019 实验室设备网 版权所有 |