分组统计查询

一、统计函数
count(*| [distinct] 字段)、max(字段)、min(字段)、sum(数字字段)、avg(数字字段)
rollback 恢复数据表内容
1.统计雇员的最高工资和最低工资和雇用的最高日期和最低工资

select max(sal),min(sal) from emp;
select max(hiredate),min(hiredate) from emp;

2.统计雇员的平均服务年限(trunc 去除小数部分)

select  trunc (avg(months_between(sysdate,hiredate)/12) )from emp;

3.count(*) 、count(字段)、count(distinct 字段)

count(*) 返回表中的每一个数据个数
count(字段) 不统计为null的数据，如果某一列不为空则返回的结果与count(*)相同
count(distinct 字段) 统计消除重复后的数据个数

①按照职位分组，统计出每个职位的名称、人数、平均工资

select job,count(empno),avg(sal)
from emp
group by job;

②正确和错误的写法
限制一：

select  count(empno) from emp;
select count(empno),ename from emp;(错误写法)
select count(empno),ename from emp group by ename;

必须进行分组查询才正确

group.png

限制二：
在使用group by 分组时，select子句之中只能出现group by中的子句

select ename,count(empno) from emp group by ename ;
select ename,count(empno) from emp group by job;(错误语法)

9Y(UX@U5$QPONN3$EI1L9%G.png

限制三：
分组查询允许嵌套查询，但是嵌套之后不允许在select之中出现任何字段。

select deptno,max(avg(sal)) from emp group by deptno;
select max(avg(sal)) from emp group by deptno;

9Y(UX@U5$QPONN3$EI1L9%G.png

③统计每个部门的名称，人数、平均工资

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

④查询每个部门的编号，名称，位置，部门人数，平均服务年限

select d.deptno,d.dname,d.loc,count(empno),avg(months_between(sysdate,hiredate)/12)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;

⑤having分组后的过滤条件,where不能进行条件的判断
查询雇员平均工资大于2000的和平均工资

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

9Y(UX@U5$QPONN3$EI1L9%G.png

where 发生在group by操作之前，属于分组前的数据筛选，不允许使用统计函数；
having 发生在group by操作之后，属于分组之后的数据筛选。
⑥显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且满足从事同一工作的雇员月工资合计大于5000，输出结果按月工资的合计升序排序
第一步：显示非销售人员

select*
from emp
where job<>'SALESMAN';

第二步：工作名称以及从事同一工作雇员的月工资总和

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

第三步：并且满足从事同一工作的雇员月工资合计大于5000

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

第四步：输出结果按月工资的合计升序排序

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

二、子查询
在一个查询语句里面嵌套其它的查询语句
1.在where子句里面使用子查询
①返回单行单列
查询出低于公司平均工资的雇员信息

select sal
from emp
where sal<(select avg(sal) from emp);

公司最早雇佣的雇员信息

select*
from emp
where hiredate=(select  min(hiredate) from emp);

单行单列.png

②多行单列
表示的是一个操作范围，提供三个操作符：in、any、all
(1)in 指的是子查询返回的内容相同

select* from emp 
where sal in
(select sal from emp where job='MANAGER');

select* from emp 
where sal not in
(select sal from emp where job='MANAGER');

not in 不会返回空的内容
(2)any

select* from emp where sal>any
(select sal from emp where job='MANAGER');

大于any 比返回的最小值要大

select* from emp where sal<any
(select sal from emp where job='MANAGER');

小于any比查询的最大值要小
(3) all

select* from emp where sal>all
(select sal from emp where job='MANAGER');

大于all 比子查询的最大值要大

select* from emp where sal<all
(select sal from emp where job='MANAGER');

小于all 比子查询的最小值要小

2.在select里面使用子查询
查询每个雇员的编号、姓名、职位、部门名称

select e.empno,e.ename,e.job,
          (select d.dname from dept d where  
            d.deptno=e.empno)
from emp e;

每当有一行emp的数据出现，就要查询一次dept表，就出现了“N+1”查询，这种操作一般不会有人去使用。

3.在from里面使用子查询
查询部门的编号、姓名、位置，人数

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

多表查询可以实现统计，子查询也可以实现统计，那么使用那种方式更好？
假设将数据表中的数据扩大100倍，此表共有400条记录。
①使用多表查询及分组统计
emp表有1400条记录 * dept表有400条记录 =560000条记录，产生笛卡尔积。
②使用子查询
子查询的数据量：只使用emp的1400条记录，最多返回400行；
子查询返回的数据量400条 * dept表的400条=160000条记录；
两个操作加起来总共：160400条记录
在实际操作中，子查询主要是解决多表查询所带来的性能问题。

总结：如果是子查询，首先应该先考虑where或from中使用子查询，select子查询可以忽略掉，而having子句出现子查询只有在使用统计函数的时候才会使用，子查询主要用于解决多表查询所带来的笛卡尔积影响性能所带来的问题。