Oracle

2023-10-22 05:57| 来源: 网络整理| 查看: 265

上一篇👉:Oracle-----统计函数&分组统计(group by)

总目录👉震惊！史上最菜的Oracle 11g教程(大佬勿进)

文章目录 1、多表查询与分组统计（重点）1.1 范例1：查询出每个部门的名称、部门人数、平均工资1.2 范例2：查询每个部门的编号、名称、位置、部门人数、平均工资 2、having 子句2.1 范例1：错误的代码2.2 范例2：使用having子句 3、分组案例3.1 范例1：显示所有非销售人员的工作名称以及从事同一工作的雇员的月工资的总和，并且要求满足从事同一工作雇员的月工资的合计大于5000，显示的结果按照月工资的合计的升序排列。3.2 范例2：统计所有领取佣金和不领取佣金的人数、平均工资

🚴大家好！我是近视的脚踏实地，这篇文章主要是来学习Oracle的多表查询与分组统计，以及having子句以及分组案例唯有行动才能解除你所有的不安

1、多表查询与分组统计（重点）

对于group by子句而言是在where子句之后执行的，所以在使用时可以进行限定查询，也可以进行多表查询

1.1 范例1：查询出每个部门的名称、部门人数、平均工资

范例1：查询出每个部门的名称、部门人数、平均工资 1、确定要使用的数据表： ※ dept表：部门名称、部门人数 ※ emp表：统计数据

2、确定已知的关联字段： ※ 部门与雇员emp.deptno=dept.deptno.

第一步：换个思路，查询出每个部门的名称、雇员编号(count(empno))、基本工资(avg(sal))

select d.dname,e.empno,e.sal from emp e,dept d where e.deptno = d.deptno;

在这里插入图片描述第二步：此时的查询结果中对于部门名称的部分出现了重复的内容，按照分组来讲，只要是出现了数据的重复那么就可以进行分组，只不过此时的分组是针对于临时表(查询结果)。既然已经确定dname上存在有重复记录，那么就直接针对于dname分组即可。

select d.dname,count(e.empno),avg(e.sal) from emp e,dept d where e.deptno = d.deptno group by d.dname;

在这里插入图片描述第三步：在dept表中实际上存在有四个部门信息，而此时的要求也是统计所以部门名称，如果发现数据不完整，立刻使用外连接。

select d.dname,count(e.empno),avg(e.sal) from emp e,dept d where e.deptno(+) = d.deptno group by d.dname;

在这里插入图片描述

1.2 范例2：查询每个部门的编号、名称、位置、部门人数、平均工资

范例2：查询每个部门的编号、名称、位置、部门人数、平均工资 1、确定要使用的数据表： ※ dept表：编号、名称、位置 ※ emp表：统计信息

2、确定已知的关联字段： ※ 部门与雇员emp.deptno=dept.deptno.

第一步：查询每个部门的编号、名称、位置、雇员编号(count())、工资(avg(sal))

select d.deptno,d.dname,d.loc,e.empno,e.sal from emp e,dept d where e.deptno(+)= d.deptno;

在这里插入图片描述第二步：此时发现有三个列(dept表)同时发生着重复，那么就可以进行多字段分组。

select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) from emp e,dept d where e.deptno(+)= d.deptno group by d.deptno,d.dname,d.loc;

在这里插入图片描述这个就是多字段分组，那么分组是根据三个字段分组，但是select的时候可以根据自己的需要选择是否要显示，不一定三个都要显示，但是返过来不行，分组只根据一个字段时，select字段只能是选择那一个字段。

2、having 子句

现在要求查询出每个职位的名称，职位的平均工资，但是要求显示的职位的平均工资高于2000。即：按照职位先进行分组，同时统计出每个职位的平均工资；随后要求只显示那些平均工资高于2000的职位信息。那么既然现在要针对于显示的数据进行筛选，自然就会首先想到where子句，于是就写了如下代码👇:

2.1 范例1：错误的代码

范例1：错误的代码

select job,avg(sal) from emp where avg(sal) >2000 group by job;

在这里插入图片描述此时直接告诉用户，where子句上不允许出现统计函数(分组函数)。因为group by子句是在where子句之后执行的，那么此时执行where子句时还没有进行分组，那么就自然无法进行统计。所以在这样的情况下，就必须使用另外一个子句完成：having子句，而此时的SQL语法结构变为如下形式👇: 【⑤选出所需要的数据列】select [distinct] * | 分组列 [别名],分组列 [别名],分组列 [别名]… 【①确定数据来源(行与列的集合）】from 表名称 [别名]，表名称 [别名]… 【②筛选数据行】[where 限定条件(s)] 【③针对于筛选的行分组】[group by 分组字段，分组字段，分组字段…] 【④针对于筛选的行分组】[having 分组过滤] 【⑥数据排序】[order by 排序字段 [ASC | DESC],排序字段 [ASC | DESC],…]

2.2 范例2：使用having子句

范例2：使用having子句

select job,avg(sal) from emp group by job having avg(sal)>2000;

在这里插入图片描述 having是在group by 分组之后才执行的筛选，在having里面可以直接使用统计函数。

说明：关于where与having的区别 ※ where子句是在group by分组之前进行筛选，指的是选出那些可以参与分组的数据，并且 where子句之中不允许使用统计函数；

※having子句是在group by分组之后执行的，那么可以使用统计函数

3、分组案例

下面通过两个具体的程序来进行分组统计的操作总结。

3.1 范例1：显示所有非销售人员的工作名称以及从事同一工作的雇员的月工资的总和，并且要求满足从事同一工作雇员的月工资的合计大于5000，显示的结果按照月工资的合计的升序排列。

范例1：显示所有非销售人员的工作名称以及从事同一工作的雇员的月工资的总和，并且要求满足从事同一工作雇员的月工资的合计大于5000，显示的结果按照月工资的合计的升序排列。

第一步：查询所有非销售人员的信息，where进行限定查询。

select * from emp where job'SALESMAN';

在这里插入图片描述第二步：按照职位进行分组，而后求出月工资的总支出；

select job,sum(sal) from emp where job'SALESMAN' group by job;

在这里插入图片描述第三步：分组后的数据进行再次筛选，使用having子句

select job,sum(sal) from emp where job'SALESMAN' group by job having sum(sal)>5000;

在这里插入图片描述第四步：按照月工资的合计升序排列，使用order by。

select job,sum(sal) from emp where job'SALESMAN' group by job having sum(sal)>5000 order by sum(sal); 或者 select job,sum(sal) sum from emp where job'SALESMAN' group by job having sum(sal)>5000 order by sum;

在这里插入图片描述