分组统计查询

最新推荐文章于 2021-04-21 11:37:32 发布

JYLM

最新推荐文章于 2021-04-21 11:37:32 发布

阅读量1.6k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/m0_37905429/article/details/77950806

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.统计函数

组函数	描述
count(*\|[distinct] 列)	求出全部的记录数
sum(列)	求出总和,操作的列是数字
avg(列)	平均值
max(列)	最大值
min(列)	最小值
median(列)	返回中间值

统计函数的应用
1. 查询出公司每个月支出的月工资的总和
select sum(sal)
from emp;
--1.1支出的奖金总和
select sum(comm)
from emp;
/* sum函数的作用
获取出查询结果中某列非空列值之和*/
2查询出公司的最高工资、最低工资、平均工资
select max(sal),min(sal),avg(sal）
from emp;
--2.1
select max(comm),min(nvl(comm,0)),avg(comm）
from emp;
3统计出公司最早雇佣和最晚雇佣的雇佣日期
--越早出现的时间越小，越迟出现的时间越大
select min(hiredate),max(hiredate)
from emp;
4统计公司中间的工资值
select median(sal)
from emp;
5 统计出公司的雇员人数
select count(*)
from emp;
--5.1
select count(distinct deptno)
from emp;
--5.2
select count(distinct comm)
from emp;

2.分组统计

2.1单字段分组统计

分组就是将全部的数据按照一定的条件拆分成一个个部分数据
分组统计语法:
select [distinct] 分组字段 [as] [列别名],...|
统计函数 [as] [别名],...
from 表名称1 [表别名 1],表名称2 [表别名 2],...
[where 条件(s)]
[group by 分组字段]
[order by 排序字段 asc | desc];
group by子句是写在where子句之后的,并且需要指定一个分组的字段

单字段分组统计应用
1统计出每个部门的人数
/*select count(*)
from emp;*/
select deptno, count(*)
from emp
group by deptno;
--第一步：获取每个部门的员工信息，左外连接
select d.*,e.*
from dept d
left outer join emp e
on(d.deptno=e.deptno);
--第二步:对第一部的查询结果
select d.deptno,count(e.empno)
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;
2统计出每种职位的最低工资和最高工资
select  job,min(sal),max(sal)
from emp
group by job;
select  job,min(nvl(sal,0)),max(sal)
from emp
group by job;
3.求出每个部门平均工资中的最高的平均工资

--第一步：获取每个部门的员工信息
select d.*,e.*
from dept d
left outer join emp e
on(d.deptno=e.deptno);
--第二步：获取每个部门的平均工资
select d.deptno,avg(sal)
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;

select d.deptno,avg(nvl(e.sal,0))
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;
--第三步：获取每个部门的平均工资中的最高的平均工资
select max(avg(sal))
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;

select max(avg(nvl(e.sal,0)))
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;
/*
select d.deptno, max(avg(nvl(e.sal,0)))
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;*/
4查询每个部门的名称、部门人数、部门平均工资、 平均服务年限
--第一步：获取每个部门的员工信息
select d.*,e.*
from dept d
left outer join emp e
on(d.deptno=e.deptno);
--第一步：按部门分组，获取每个部门的名称，
select d.dname,count(e.empno),avg(nvl(e.sal,0)),
avg(months_between(sysdate,hiredate)/12)
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.dname;
5.查询公司各个工资等级雇员的数量、平均工资
--第一步：查出每个工资等级的雇员
select s.grade,avg(e.sal)
from salgrade s
left outer join emp e
on(e.sal between s.losal and s.hisal);
--第二步：按工资等级分组，查询各个工资等级雇员的数量、平均工资
select s.grade,avg(e.sal),count(e.empno)
from salgrade s
left outer join emp e
on(e.sal between s.losal and s.hisal)
group by s.grade;
6.统计出领取佣金与不领取佣金的雇员的平均工资、平均雇佣年限、雇员人数
--第一步：查询领取佣金的雇员的平均工资、平均雇佣年限、雇员人数
select avg(sal),avg(months_between(sysdate,hiredate)/12),count(empno)
from emp
where comm is not null and nvl(comm,0)>0;

/*
这条语句会出现错误，原因是：在没有group by 字句的select字句中出现了统计函数，就不能出现单个字段
select avg(sal),avg(months_between(sysdate,hiredate)/12),count(empno)
from emp
where comm is not null and nvl(comm,0)>0;*/

--第二步：查询领取不佣金的雇员的平均工资、平均雇佣年限、雇员人数

select avg(sal),avg(months_between(sysdate,hiredate)/12),count(empno)
from emp
where comm is null or nvl(comm,0)<=0;
--第三步：统计出领取佣金与不领取佣金的雇员的平均工资、平均雇佣年限、雇员人数
select avg(sal),avg(months_between(sysdate,hiredate)/12),count(empno)
from emp
where comm is not null and nvl(comm,0)>0
union
select avg(sal),avg(months_between(sysdate,hiredate)/12),count(empno)
from emp
where comm is null or nvl(comm,0)<=0;

分组操作注意事项

1 如果没有group by子句,则在select子句中出现统计函数, 其他任何字段都不允许出现

2 如果有group by子句,select子句后只允许出现分组字段和统计函数,而其他的非分组字段不能使用.

3 统计函数允许嵌套使用,但是嵌套统计函数之后的select子中不允许再出现任何的字段,包括分组字段.

sql语句中加入了group by子句之后的执行顺序

(1) 执行from子句,确定要检索的数据来源

(2) 执行where子句,使用限定符对数据进行过滤

(3) 执行group by子句,根据指定字段进行分组

(4) 执行select子句,确定要检索出的分组字段以及编写相应统计函数

(5) 执行order by子句的排序

2.2 多字段分组统计

多字段分组统计语法：

select [distinct] 分组字段1 [as] [列别名],

[分组字段2 [as] [列别名],...]

| 统计函数 [as] [别名],...

from 表名称1 [表别名 1],表名称2 [表别名 2],...

[where 条件(s)]

[group by 分组字段1 ,分组字段2,... ]

[order by 排序字段 asc | desc];

多字段查询应用：

1.查询出每个部门的编号、名称、位置、部门人数、
平均工资、总工资、最高工资、最低工资
--第一步：查询出每个部门的编号、名称、位置、以及该部门的员工信息
select d.deptno,d.dname,d.loc,e.*
from dept d
left outer join emp e
on d.deptno=e.deptno;
--第二步：查询出每个部门的编号、名称、位置、部门人数、平均工资、总工资、最高工资、最低工资
select d.deptno,d.dname,d.loc,count(e.empno),avg(nvl(sal,0)),sum(nvl(e.sal,0)),max(nvl(e.sal,0)),min(nvl(e.sal,0))
from dept d
left outer join emp e
on d.deptno=e.deptno
group by d.deptno,d.dname,d.loc;

select d.deptno,d.dname,d.loc,count(e.empno),avg(sal),sum(e.sal),max(e.sal),min(e.sal)
from dept d
left outer join emp e
on d.deptno=e.deptno
group by d.deptno,d.dname,d.loc;





having子句作用

having子句完成对分组之后的数据进行再次过滤，对分组之后的数据进行再次过滤的语法

select [distinct] 分组字段1 [as] [列别名],

分组字段2 [as] [列别名],...]

| 统计函数 [as] [别名],...

from 表名称1 [表别名 1],表名称2 [表别名 2],...

[where 条件(s)]

[group by 分组字段1 ,分组字段2,... ]

[having 过滤条件(s)]

[order by 排序字段 asc | desc];

having子句作用

1 查询出所有平均工资大于2000元的职位信息、平均工资、
  雇佣人数
  --第一步：查询出每个职位的平均工资，雇佣人数
  select job,avg(sal),count(empno)
  from emp
  group by job;
  --第二步：查询出所有平均工资大于2000元的职位信息、平均工资、雇佣人数
  select job,avg(sal),count(empno)
  from emp
  group by job
  having avg(sal)>2000;
 
2 列出至少一个员工的所有部门编号、名称,并统计出这些
  部门的平均工资、最低工资、最高工资.
  --第一步：查询出每个部门编号、名称，以及该部门的员工信息
  select d.deptno,d.dname,e.*
  from dept d
  left outer join emp e
  on d.deptno=e.deptno;
  --第二步：按部门名称，编号进行分组查询出每个部门的部门编号、名称，平均工资、最低工资、最高工资.
    select d.deptno,d.dname,avg(nvl(e.sal,0)),min(nvl(e.sal,0)),max(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on d.deptno=e.deptno
  group by d.deptno,d.dname;
  --第三步：对分组进行过滤，只显示满足having中条件表达式的分组数据，从而查出至少一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资.
   select d.deptno,d.dname,avg(nvl(e.sal,0)),min(nvl(e.sal,0)),max(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on d.deptno=e.deptno
  group by d.deptno,d.dname
  having count(e.empno)>=1;
3 显示非销售人员工作名称以及从事同一工作雇员的月工资
  的总和,并且要满足从事同一工作的雇员的月工资合计
  大于5000元输出结果按月工资的合计升序排列.
  --第一步：查询出每一个工作的雇员月工资总和
  select job,sum(sal)
  from emp
  group by job;
  --第二步：查询出非销售人员从事的每一个工作的的月工资总和
    select job,sum(sal)
  from emp
  where job<>'SALESMAN'
  group by job;
  --第三步：过滤部分分组，查询月工资合计大于5000元的工作，以及该工作的月工资总和
   select job,sum(sal)
  from emp
  where job<>'SALESMAN'
  group by job
  having sum（sal）>5000;
  --第四步：/*对第三步的查询结果按月工资的合计升序排列.
  从而非销售人员工作名称以及从事同一工作雇员的月工资
  的总和,并且要满足从事同一工作的雇员的月工资合计
  大于5000元输出结果按月工资的合计升序排列.*/
   select job,sum(sal)
  from emp
  where job<>'SALESMAN'
  group by job
  having sum（sal）>5000
  order by sum(sal) asc;
  六.子查询
  1.查询公司中工资最低的雇员的完整信息
  --第一步：查询公司中的最低工资
  select min(sal)
  from emp;
  --第二步：查询公司中雇员的完整信息
  select *
  from emp;
  --第三步：查询公司中工资最低的雇员的完整信息
 /* select *
  from emp
  where sal=800;*/
  select *
  from emp
  where sal=( select min(sal)
  from emp); //作为逻辑表达式的操作数
  2.子查询返回单行单列数据
2-1 查询出基本工资比ALLEN低的全部雇员信息
  --第一步：查询出ALLEN的基本工资
  select sal
  from emp
  where ename='ALLEN'; 
   --第二步：查询出全部雇员信息
    select *
  from emp;
   --第三步：查询出基本工资比ALLEN低的全部雇员信息
   select *
  from emp
  where sal<( select sal
  from emp
  where ename='ALLEN' );
2-2 查询基本工资高于公司平均薪资的全部雇员信息
  --第一步：查询公司平均薪资
  select avg(sal)
  from emp;
   --第二步：查询全部雇员信息
     select *
  from emp;
  --第三步： 查询基本工资高于公司平均薪资的全部雇员信息
    select *
  from emp
  where sal>(  select avg(sal)
  from emp);
2-3 查找出与ALLEN从事同一工作,并且基本工资高于
  雇员编号为7521的全部雇员信息.
   --第一步： ALLEN从事的工作,
    select job
  from emp
  where ename='ALLEN';
  --第二步： 查找出编号为7521的雇员的基本工资
    select sal
  from emp
  where empno=7521;
  --第三步：查询全部雇员信息
       select *
  from emp;
   --第四步： 查找出与ALLEN从事同一工作,并且基本工资高于雇员编号为7521的全部雇员信息.
select *
from emp
where job=(  select job
  from emp
  where ename='ALLEN')
  and sal>( select sal
  from emp
  where empno=7521)
  and ename<>'ALLEN';







sql中加入了having子句之后的执行顺序

(1) 执行from子句,确定要检索的数据来源.

(2) 执行where子句,使用限定符对数据进行过滤.

(3) 执行group by子句,根据指定字段进行分组.

(4) 执行having子句,对分组后的统计数据进行过滤.

(5) 执行select子句,确定要检索出的分组字段以及编写相应的统计函数

(6) 执行order by子句排序.

where子句和having子句的区别

where子句: 是在分组之前使用,表示从所有数据中筛选出数据,以完成分组的要求,在where子句中不允许使用统计函数,没有group by子句也可以使用.

having子句: 是在分组之后使用的,表示对分组统计后的数据执行再次过滤,可以使用统计函数,有group by子句之后才可以出现having子句.