oracle-分组统计查询和子查询

谁借莪１个温暖的怀抱￠ 2022-01-29 08:47 596阅读 0赞

范例：统计所有雇员的人数，支付的总工资，平均工资，最高工资，最低工资

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select count(empno),sum(sal),avg(sal),max(sal),min(sal) from emp;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="120" src="https://img-blog.csdnimg.cn/20190521161428602.png" width="863"></p> </td> 
  </tr> 
 </tbody> 
</table>

范例：统计带工薪的总工资和平均工资

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select sum(sal+nvl(comm,0)*12) ,avg(sal+nvl(comm,0)*12) from emp;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="139" src="https://img-blog.csdnimg.cn/20190521161428605.png" width="790"></p> </td> 
  </tr> 
 </tbody> 
</table>

面试题：请解释count(\*) count(字段)count（distinct 字段）的区别？

1.  count（\*）：明确返回表中数据的条数
2.  count（字段）：不统计为null的数据条数，如果不为null，那么就等于count（\*）
3.  count（distinct 字段）：统计消除重复数据后的数据条数

在实现具体操作之前必须要先解决一个问题，什么情况下可以分组？

·例如：全班，男生一组，女生一组，互相拔河

·例如：全部，戴眼镜一组，不戴眼镜一组，互相比裸眼视力

·例如：25岁之前一组，25岁之后一组

实际上所谓的分组，指的是某些群体具备共同的特征。现在回到emp表中，可以发现如果存在有重复的数据，那么就证明这两个字段上都可以进行分组。

但是一定要记住，实际上一条记录也可以进行分组。只不过这类的做法是没有意义的。可以采用如下语法完成分组。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Select [distinct] * |列名称 [别名],列名称 [别名],…|统计函数</p> <p style="margin-left:0cm;">From 数据表 [别名]，数据表 [别名],…</p> <p style="margin-left:0cm;">[where 条件(s)]</p> <p style="margin-left:0cm;">[group by 分组字段，分组字段,…]</p> <p style="margin-left:0cm;">[order by 字段 [ASC|DESC]];</p> </td> 
  </tr> 
 </tbody> 
</table>

范例：按照职位进行分组，统计出每个职位的平均工资、最高工资和最低工资、人数。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select<span style="color:#FF0000;"> job </span>,<strong>avg(sal),max(sal),min(sal),count(empno) </strong>from emp group by <span style="color:#FF0000;">job;</span></p> <p style="margin-left:0cm;"><span style="color:#FF0000;">1</span><span style="color:#FF0000;">、</span><span style="color:#FF0000;">group by </span><span style="color:#FF0000;">和第一个字段相同；</span></p> <p style="margin-left:0cm;"><span style="color:#FF0000;">2</span><span style="color:#FF0000;">、后边的都是统计函数否则会出错；</span></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="223" src="https://img-blog.csdnimg.cn/20190521161428631.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1014"></p> </td> 
  </tr> 
 </tbody> 
</table>

范例：按照部门编号进行分组，统计出每个部门的人数，平均工资，平均服务年限

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select deptno ,count(empno),avg(sal),trunc(avg((sysdate-hiredate)/12)) from emp group by deptno;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="162" src="https://img-blog.csdnimg.cn/20190521161428647.png" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

使用统计函数的限制：

1.  统计函数单独使用时，没有group by子句，只能够出现统计函数；
2.  使用统计函数时，存在group by子句，select 子句只能够出现分组字段，其他任何字段字段都不能出现；
3.  统计函数在分组之中可以嵌套使用，但是嵌套之后的统计查询之中不能出现任何字段，包括分组字段；

以上的操作都是针对于单张表一个字段的分组，那么通过之前给出的语法可以设置多个分组字段，那么就要求这多个字段必须同时重复。

范例：查询出每个部门的名称，部门人数和平均工资。

|- 确立使用的数据表：

dept表：找到部门名称；

emp表：统计部门人数、平均工资。

|- 确立已知的关联字段

雇员和部门关联：emp.deptno=dept.deptno;

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">1、先不考虑分组的问题，换个角度，实现查询；查询出每个部门的名称，雇员编号，平均工资</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select e.empno,d.dname,sal from emp e,dept d where e.deptno = d.deptno;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="407" src="https://img-blog.csdnimg.cn/20190521161428672.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="936"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">2、在以上查询的过程中，发现了部门的名称数据是重复的，但是这个时候并不是对原始表进行操作，而是对这个临时表进行操作。</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select d.dname,count(e.empno),avg(e.sal) from emp e,dept d where e.deptno=d.deptno group by d.dname;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="156" src="https://img-blog.csdnimg.cn/20190521161428684.png" width="1131"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">但是，有四个部门的数据，所以得建立右外连接，左边加➕号</p> <p style="margin-left:0cm;">&nbsp;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select d.dname,count(e.empno),avg(e.sal) from emp e,dept d where e.deptno(+)=d.deptno group by d.dname;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="190" src="https://img-blog.csdnimg.cn/20190521161428694.png" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

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

|- 确定所需要的数据表

1.  emp表：找到部门编号，名称，位置
2.  dept表：统计部门人数，平均工资

|- 确定已知的关联字段

雇员和部门关联：emp.deptno = dept.deptno;

<table style="margin-left:21pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select d.deptno,d.dname,d.loc,e.empno,e.sal from emp e, dept d where e.deptno=d.deptno;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="414" src="https://img-blog.csdnimg.cn/20190521161428724.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1058"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">此时，最重要的是发现了临时表里边有deptno，loc，dname数据存在重复，并且想要对部门人数和平均工资使用统计函数，那么就用三个分组字段，并且使用右外连接</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">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;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="383" src="https://img-blog.csdnimg.cn/20190521161428765.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

错误：

<table style="margin-left:21pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select job ,avg(sal)</p> <p style="margin-left:0cm;">from emp</p> <p style="margin-left:0cm;"><span style="color:#FF0000;">where avg(sal)&gt;2000</span></p> <p style="margin-left:0cm;">&nbsp;group by job;</p> <p style="margin-left:0cm;">&nbsp;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="153" src="https://img-blog.csdnimg.cn/20190521161428785.png" width="771"></p> </td> 
  </tr> 
 </tbody> 
</table>

原因：

where子句之中不能够使用统计函数，where是在group by之间执行的，而此时的条件明显是在group by之后执行，所以使用having子句完成。

<table style="margin-left:21pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select job,avg(sal) from emp group by job<span style="color:#FF0000;"> having avg(sal)&gt;2000;</span></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="154" src="https://img-blog.csdnimg.cn/20190521161428794.png" width="822"></p> </td> 
  </tr> 
 </tbody> 
</table>

sql语句执行顺序：

1.  from子句确定数据来源
2.  where子句过滤数据行
3.  group子句执行分组操作
4.  having子句针对于分组之后进行数据过滤
5.  select子句确定数据列
6.  orderby子句进行数据排序

二、子查询

下面情况适用于大部分子查询：

1.  子查询返回单行单列：having、where
2.  子查询返回单行多列：where
3.  子查询返回多行多列：from

范例：子查询返回单行单列è要求统计出所有高于公司平均工资的全部雇员信息

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp where emp.sal&gt;(select avg(sal) from emp);</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="220" src="https://img-blog.csdnimg.cn/20190521161456242.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

范例：子查询返回多行多列è要求找到与SCOTT工资相同，职位相同的雇员信息

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select sal,job from emp where (sal,job)=(select sal,job from emp where emp.ename='SCOTT');</p> <p style="margin-left:0cm;">注意：此时‘字符串’是区分大小写的，找不到小写字母的名字</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="137" src="https://img-blog.csdnimg.cn/20190521161456246.png" width="1054"></p> </td> 
  </tr> 
 </tbody> 
</table>

IN：指的是在指定范围之内查询è

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp where sal in(select sal from emp where job='MANAGER');</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="156" src="https://img-blog.csdnimg.cn/20190521161456254.png" width="1190"></p> </td> 
  </tr> 
 </tbody> 
</table>

使用not in的时候必须注意此时不能包含null，否则会进行全选。

·>ANY：比子查询的最小值要大；

·<ANY：比子查询的最大值要小；

·>ALL：比子查询的最大值要大；

·<ALL：比子查询的最小值要小       ；

在having子句中使用子查询意味着进行了分组查询。

范例：查询高于公司平均工资的部门编号和平均工资

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select deptno ,avg(sal) from emp group by deptno having avg(sal)&gt;(select avg(sal) from emp);</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="128" src="https://img-blog.csdnimg.cn/20190521161456270.png" width="1105"></p> </td> 
  </tr> 
 </tbody> 
</table>

范例：查询出平均工资最低的职位信息、人数和平均工资

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select job,avg(sal) from emp group by job having avg(sal) =(select min(avg(sal)) from emp group by job);</p> <p style="margin-left:0cm;">select job,count(empno),avg(sal) from emp where sal=(select min(avg(sal)) from emp group by job ) group by job;</p> <p style="margin-left:0cm;">&nbsp;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="280" src="https://img-blog.csdnimg.cn/20190521161456274.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

在from子句之中使用子查询。from子句主要功能是确定数据来源，而且数据来源是数据表，表是一种行和列的集合。from子句里边出现的子查询，其返回的结果一定是多行多列数据。

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

|- 确定表：

1.  emp表：部门的人数，平均工资
2.  dept表：部门的编号，名称，位置

|- 关联的字段：

emp.deptno = dept.deptno;

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) from emp e,dept d group by d.deptno,d.dname,d.loc;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="188" src="https://img-blog.csdnimg.cn/20190521161456298.png" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

通过以上的程序虽然实现了与之前多个字段分组相同的功能，但是代码实在是过于复杂了。形成的笛卡尔积很大，造成了性能的下降，所以使用子查询。

范例：要求查询出高于公司平均工资的雇员姓名，职位，工资，领导姓名、部门名称，部门人数，部门平均工资，工资等级的雇员人数。

|-确定要使用的数据表

1.  emp表：统计公司的平均工资
2.  emp表：雇员姓名、职位、工资、领导姓名
3.  dept表：部门的名称
4.  emp表：部门的人数、平均工资
5.  salgrade表：工资等级
6.  salgrade表：统计一个工资等级的人数

|- 确立已知的关联字段：

1.  emp.mgr = emp2.mgr
2.  emp.deptno = dept.deptno;
3.  emp.sal between salgrade.losal and hisal;

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select e.ename 雇员姓名,e.job 雇员职位，e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位 from emp e,emp m where e.sal&gt;(select avg(sal) from emp) and e.mgr=m.empno(+);</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="211" src="https://img-blog.csdnimg.cn/20190521161456307.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="998"></p> </td> 
  </tr> 
 </tbody> 
</table>

加入dept表，找到部门名称

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select e.ename 雇员姓名,e.job 雇员职位，e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位,d.dname 部门名称 from emp e,emp m ,dept d where e.sal&gt;(select avg(sal) from emp) and e.mgr=m.empno(+) and e.deptno = d.deptno;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="270" src="https://img-blog.csdnimg.cn/20190521161456334.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select e.ename 雇员姓名,e.job 雇员职位，e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位,d.dname 部门名称,dtemp.count 部门人数 ,s.grade 工资等级 from emp e,emp m ,dept d,(select deptno dno,count(empno) count from emp group by deptno) dtemp ,salgrade s where e.sal&gt;(select avg(sal) from emp) and e.mgr=m.empno(+) and e.deptno = d.deptno and dtemp.dno=d.deptno and e.sal between s.losal and s.hisal;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="282" src="https://img-blog.csdnimg.cn/20190521161456380.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="482" src="https://img-blog.csdnimg.cn/20190521161456378.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>