3.分组查询

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;