1.分组查询 group by
定义:按照一定的规则分组,统一分析各组的情况,每一个组返回一个结果值
聚合函数(分组函数)
sum() 求和
avg() 平均
max() 最大
min() 最小
count() 统计计数
select count(empno) from emp; ----14 --主键列:非空且唯一
select count(comm) from emp; ----5 --comm 有空值,空值不参与计数
count(列名)---count()中传具体的列名时,返回该列不为空的值的个数
select count(*) from emp; ----返回当前表中的记录数(数据条数)
count(常量)---返回当前表中的记录数
select count(1) from emp; 一般在工作中用数字1
分组语法
select *|列|函数|常量|表达式 ----5
from 表名 ------1
where 分组前的过滤条件 ------2
group by 分组后的内容 -----3
having 分组的过滤条件 ------4
order by 排序的内容 asc|desc -----6
运行顺序 : from - where -group by -having - select - order by
扩展:
1.select deptno from emp group by deptno; ---实现去重
2.select sum(sal) from emp group by deptno; ---在分组查询时,group by 后边出现的字段,select 后边可以不去展示。
3.select deptno,ename,sum(sal) from emp group by deptno; ---报错:不是group by 表达式。在分组查询时,select 后边出现的字段,gorup by 后边必须得有。
select deptno,ename,sum(sal) from emp group by deptno,ename; ---按部门和姓名两个字段分组,取到的是每个人的总工资。
注意点
1. group by 后边出现的字段,select 后边可以不去展示
2. select 后边想要展示某个字段,就必须按照该字段分组,也就是说 group by 后边必须得有
--注意:select后用了聚合函数,除此之外,还需要展示其他字段,要展示的其他字段必须出现在gorup by 后边
select max(sal) ,ename from emp; ---报错:不是单租分组函数
select max(sal) ,ename from emp group by ename; --每个人的最大工资
2.分组查询练习
where hiredate<to_date('1982-1-1','yyyy-mm-dd')
group by deptno,job
having avg(sal)>800
order by max(sal) desc;
--2.查询部门人数超过3的部门的部门编号、最高薪资、最低薪资、部门人数,按部门人数升序排列
select deptno,max(sal),min(sal),count(1)
from emp
group by deptno
having count(1)>3
order by count(1) asc;
--3.查询岗位名称不以S开头、并且岗位平均薪资超过2000的岗位的 岗位名称、岗位人数、岗位平均薪资,按岗位平均薪资升序排列
select job,count(1),avg(sal)
from emp
where job not like 'S%'
group by job
having avg(sal)>2000
order by avg(sal) asc;
--4.查询各部门部门编号、最高薪资、最低薪资,以及最高和最低薪资的差值,按部门编号降序排列
select deptno,max(sal),min(sal),max(sal)-min(sal)
from emp
group by deptno
order by deptno desc;
--5.统计岗位类型、最高薪资、最低薪资、薪资合计、平均薪资,以及人数,各字段以中文列名展示
select job 岗位类型,
max(sal) 最高薪资,
min(sal) 最低薪资,
sum(sal) 薪资合计,
avg(sal) 平均薪资,
count(1) 人数
from emp
group by job;
--6.查询姓名重复的员工的姓名
select ename from emp group by ename having count (1)>1;
--7.查询各部门下,人数超过两人的岗位有哪些,显示部门编号、岗位名称
select deptno,job,count(1) from emp group by deptno,job having count(1)>2;
--8.统计薪资在1000到3000之间的人员中,有奖金的员工总数及无奖金的员工总数
select count(comm) 有奖金,count(1)-count(comm) 无奖金 from emp where sal between 1000 and 3000;
--扩展:
分组查询练习2:
--1.统计 除10号部门 的其他部门中,哪些部门的最高薪资 达到三千
select * from emp;
select deptno, max(sal) from emp where deptno != 10 group by deptno having max(sal)>=3000;
select deptno,max(sal) from emp group by deptno having deptno!=10 and max(sal)>=3000;
--2.查询平均薪资超过1800的部门,显示部门编号及平均薪资
select deptno,avg(sal) from emp group by deptno having avg(sal)>1800;
--3.查询 薪资合计超过5000的岗位 及其平均薪资
select job ,avg(sal) from emp group by job having sum(sal)>5000;
--4.查询部门人数超过4人的部门编号及薪资合计
select deptno,sum(sal),count(1) as 人数 from emp group by deptno having count (1)>4;
--5.查询每个部门有多少人, 显示部门编号,部门人数
select deptno,count(1)as 部门人数 from emp group by deptno;
--6.每个部门员工的平均工资,去掉小于2000的工资,按照部门平均工资排名
select deptno,avg(sal) from emp where sal>=2000 group by deptno order by avg(sal) desc;
--7.每个部门的工资总和 ,按照工资总和排序
select deptno, sum(sal) from emp group by deptno order by sum(sal);
--8.求每个部门的员工数量想得到员工数量>5的部门
select deptno,count(1) from emp group by deptno having count(1)>5;
--9.查询在1981/04/13之后入职的员工,
--结果展示平均工资低于1800的部门号和该部门的平均工资
select deptno,avg(sal) from emp where hiredate>to_date('1981/04/13','yyyy--mm--dd') group by deptno having avg(sal)<1800;
--3.查询岗位名称不以S开头、并且岗位平均薪资超过2000的岗位的 岗位名称、岗位人数、岗位平均薪资,按岗位平均薪资升序排列
select job,count(1),avg(sal)
from emp
where job not like 'S%'
group by job
having avg(sal)>2000
order by avg(sal) asc;
--4.查询各部门部门编号、最高薪资、最低薪资,以及最高和最低薪资的差值,按部门编号降序排列
select deptno,max(sal),min(sal),max(sal)-min(sal)
from emp
group by deptno
order by deptno desc;
--5.统计岗位类型、最高薪资、最低薪资、薪资合计、平均薪资,以及人数,各字段以中文列名展示
select job 岗位类型,
max(sal) 最高薪资,
min(sal) 最低薪资,
sum(sal) 薪资合计,
avg(sal) 平均薪资,
count(1) 人数
from emp
group by job;
--6.查询姓名重复的员工的姓名
select ename from emp group by ename having count (1)>1;
--7.查询各部门下,人数超过两人的岗位有哪些,显示部门编号、岗位名称
select deptno,job,count(1) from emp group by deptno,job having count(1)>2;
--8.统计薪资在1000到3000之间的人员中,有奖金的员工总数及无奖金的员工总数
select count(comm) 有奖金,count(1)-count(comm) 无奖金 from emp where sal between 1000 and 3000;
--扩展:
select case when comm is not null then 1 else 0 end as 奖金情况 ,count(1)
from emp
where sal between 1000 and 3000
group by case when comm is not null then 1 else 0 end
--6.查询姓名重复的员工的姓名
select ename,count(1) from emp group by ename having count (1)>1;