谈谈SQL中的Group by
1 注意事项
- 基于Oracle 10G（03年发布版本）
- PLSQL 804
- 表数据可以参考
emp表
image.png
dept表
image.png
2 group by概念
字面理解的意思是怎么分组，那什么是分组呢？日常数据统计，常见这样的一些问题
- 每个部门有多少个人
- 每个职位都有几个人？
- 每年入职几个人？
- 每种薪资等级几个人？
以第一个问题为例，我们如果直接用excel来统计数据，通常的做法是
- 按部门排序（或者筛选出来）
- 分别统计每个部门多少个人（计数）
-
得到最终的结果
这类似于一个数据透视表的做法吧。
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还要一些其他的用法，此处不表。
总结
- 当问到每个、每种、每XX的平均、最大、总和、最小、多少的时候，需要用到group by来分组，然后进行统计（聚合函数）
- select 后的列，如果同时有分组函数，那就要group by这个列，多个列也一样
- group by 后的列，在select后可以不出现，但失去的是可读性
- 聚合数据的筛选要通过having来限定，不能放where中
- having可以限定普通的列，那么这个列一定要放在group by后面
- having和group by的顺序可以互换
