1 注意事项

1. 基于Oracle 10G（03年发布版本）
2. PLSQL 804
3. 表数据可以参考
   emp表
   image.png

dept表

image.png

2 group by概念

字面理解的意思是怎么分组，那什么是分组呢？日常数据统计，常见这样的一些问题

• 每个部门有多少个人
• 每个职位都有几个人？
• 每年入职几个人？
• 每种薪资等级几个人？

以第一个问题为例，我们如果直接用excel来统计数据，通常的做法是

1. 按部门排序（或者筛选出来）
2. 分别统计每个部门多少个人（计数）

3. 得到最终的结果
   这类似于一个数据透视表的做法吧。

   
   image.png

image.png

• 每个部门有多少个人

  select deptno,count(*)
  --再对分组后的数据进行计数
  from emp
  group by deptno
  --先分组（参考上面excel筛选数据的过程）
  

image.png

好像没错，但如果看了dept表就知道我们其实是有4个部门的，那上面的表达其实就有点问题，应该要把4个部门都给列出来的。

image.png

那我们改进下刚才的语句

select d.deptno,count(*)
from dept d left join emp e on d.deptno=e.deptno
group by d.deptno

用到了左连接，但看下结果。

image.png

不对呀，我们部门4是没人的，为何给我统计出来一个人？关键在于count(*)是包括空值的，我们要忽略空值，那可以这样，部门有人总有职员编号吧。

select d.deptno,count(empno)
from dept d left join emp e on d.deptno=e.deptno
group by d.deptno

image.png

这样的结果才是我们想要的。

• 每个职位都有几个人？

  select job,count(*)
  from emp
  group by job
  

image.png

• 每年入职几个人？

  select to_char(hiredate,'YYYY') YEAR,count(*)
  from emp
  group by to_char(hiredate,'YYYY')
  

image.png

注意oracle中要用到to_char对时间格式处理

• 每种薪资等级几个人？

  select s.grade,count(*)
  from emp e,salgrade s
  where e.sal between s.losal and s.hisal
  group by s.grade
  order by s.grade
  

image.png

注意要用到表连接

3 group by 的一些细节

group还要其他的一些细节，我们再来看看

3.1 group by后的列

• group by后面的列在select中可以不出现

  select count(*)
  from emp
  group by deptno
  

image.png

但是鬼知道你在说什么呢？这个数据是啥意思呢？所以可读性降低了。

• select 后面的列一定要在group by后有

image.png

• select 后面可以有多个列，那就是分组之后再分组

  select deptno,job,count(*)
  from emp
  group by deptno,job
  order by deptno,job
  #为了增强可读性我们加上order by
  

image.png

3.2 group by后面的having

当我们要筛选分组后的统计数据，就要用到having

• 针对问题：统计每个部门有多少人，只显示超过4个人的部门及人数
  如果我们用where来限定这个数据，会得到如下提示：

  
  image.png

  select deptno,count(*)
  from emp
  group by deptno
  having count(*)>4

image.png

而having 的顺序和group by是可以颠倒的，当然一般我们还是先group by 再having。

      select deptno,count(*)
      from emp
      having count(*)>4 
      group by deptno

havging能否限定一些非聚合函数统计数据呢？答案是肯定的！

image.png

好像不行？但看提示，我们发现它没有说having后面不能接job，说的是job不是分组的数据。那我们分组一下job试试。

select deptno,job,count(*)
from emp
group by deptno,job
having count(*)>3
and job not like 'MANAGER'

image.png

而没经过过滤的数据是怎样的呢？如下图，对比上面的结果，我们发现having是过滤了分组后的数据，不光是聚合函数，也对字段值可以过滤！！

image.png

having后面跟的聚合数据一定要在select中出现吗？不是的！
比如我们要求：部门人数超过4个的平均工资信息

select deptno,avg(sal)
from emp
group by deptno
having count(*)>4

image.png

3.3 group by的信息

• group by一定要聚合函数吗？不一定！只是没有实际意义。

  select deptno
  from emp
  group by deptno
  

• 有聚合函数一定要有group by？不一定。

     #统计所有职员的人数、平均工资、最大工资、最小工资、工资总和
    select count(*),avg(sal),max(sal),min(sal),sum(sal)
     from emp
  

• 当有单独的列，又有聚合函数的时候，就需要用到group by了

• group by还要一些其他的用法，此处不表。

总结

1. 当问到每个、每种、每XX的平均、最大、总和、最小、多少的时候，需要用到group by来分组，然后进行统计（聚合函数）
2. select 后的列，如果同时有分组函数，那就要group by这个列，多个列也一样
3. group by 后的列，在select后可以不出现，但失去的是可读性
4. 聚合数据的筛选要通过having来限定，不能放where中
5. having可以限定普通的列，那么这个列一定要放在group by后面
6. having和group by的顺序可以互换