1.6、聚合函数
-- 组函数 |聚合函数 | 多行函数 :多条记录返回一个结果
-- 确定要计算的数据然后才能使用组函数,对结果集,分组
-- count() sum() max() min() avg()
--注意: 如果select后面一旦出现组函数的使用,不能与非分组字段一起使用
-- 统计一下一共有多少个员工
select count(*) from emp;
select count(empno) from emp;
select count(deptno) from emp;
select count(1) from emp;
-- 统计一共有几个部门
select count(1) from dept;
-- 统计有员工存在的部门总数
select count(distinct deptno) from emp;
-- 统计20部门一共有多少人
select count(1) from emp where deptno = 20;
-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;
-- 查询本公司的最高工资和最低工资
select max(sal),min(sal) from emp;
--查看30部门的最高工资和最低工资
select max(sal),min(sal) from emp where deptno = 30;
-- 请查询出 20部门的平均工资
select avg(sal) from emp where deptno = 20;
-- 计算出所有员工的奖金总和
select sum(comm) from emp;
--null值不参与组函数计算
-- 统计有奖金的员工有几个
select count(comm) from emp where comm is not null;
--查询最高薪水的员工姓名,及薪水
select max(sal) from emp;
select ename,sal from emp where sal = (最高薪水);
select ename,sal from emp where sal = (select max(sal) from emp);
-- 查询工资低于整个公司平均工资的员工编号,姓名及工资
select empno,ename,sal from emp where sal<(select avg(sal) from emp);
--思考
--查看高于本部门平均薪水员工姓名
select avg(sal) from emp where deptno = 10;
select *
from emp e1
where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);
1.7、分组
-- 分组 : 根据指定的规则对数据进行分组
-- group by 分组字段
-- 语法: select 数据 from 数据源 where 行过滤条件 group by 分组 having 组过滤信息 order by 排序字段;
-- 执行流程 : from -> where -> group by -> having -> select -> order by
-- 如果 select后面一旦出现组函数,不能使用非分组字段
-- 如果一旦分组,只能使用分组字段或者组函数
-- where中不能使用字段别名,不能使用组函数,因为执行流程的问题
-- 求出每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
-- 找出20部门和30部门的最高工资
select deptno,max(sal) from emp group by deptno;
--先过滤后分组
select deptno,max(sal) from emp where deptno in(20,30) group by deptno;
--先分组后过滤
select deptno,max(sal) from emp group by deptno having deptno in(20,30);
-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal) from emp where sal>1000 and deptno in(10,20) group by deptno;
select avg(sal) from emp where sal>1000 group by deptno having deptno in(10,20);
-- 找出每个部门的最高工资
select deptno,max(sal) from emp group by deptno;
-- 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
-- 查询最低平均工资的部门编号
--1)找到每个部门的部门编号与平均工资
select deptno,avg(sal) from emp group by deptno;
--2)最低平均工资
select min(avg(sal)) from emp group by deptno;
--3)工资与最低平均工资值相等的部门的部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal)=(最低平均工资);
select deptno
from emp
group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);
-- 统计每个部门的员工数,和部门编号,按照员工个数升序排序
select deptno,count(1) n from emp group by deptno order by n ;
-- 查询每个工种的最高工资以及工种
select job,max(sal) from emp group by job;
-- 查询平均工资在1500到2000之间的部门平均工资和部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal) between 1500 and 2000;
1.8、行转列
/*
id name course score
1 张三 语文 81
2 张三 数学 75
3 李四 语文 81
4 李四 数学 90
5 王五 语文 81
6 王五 数学 100
7 王五 英语 90
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五',&