sql中group by oracle,谈谈SQL中的Group by

1 注意事项

基于Oracle 10G(03年发布版本)

PLSQL 804

表数据可以参考

emp表

dept表

2 group by概念

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

每个部门有多少个人

每个职位都有几个人？

每年入职几个人？

每种薪资等级几个人？

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

按部门排序(或者筛选出来)

分别统计每个部门多少个人(计数)

得到最终的结果

这类似于一个数据透视表的做法吧。

每个部门有多少个人

select deptno,count(*)

--再对分组后的数据进行计数

from emp

group by deptno

--先分组(参考上面excel筛选数据的过程)

复制代码

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

那我们改进下刚才的语句

select d.deptno,count(*)

from dept d left join emp e on d.deptno=e.deptno

group by d.deptno

复制代码

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

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

select d.deptno,count(empno)

from dept d left join emp e on d.deptno=e.deptno

group by d.deptno

复制代码

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

每个职位都有几个人？

select job,count(*)

from emp

group by job

复制代码

每年入职几个人？

select to_char(hiredate,'YYYY') YEAR,count(*)

from emp

group by to_char(hiredate,'YYYY')

复制代码

注意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

复制代码

注意要用到表连接

3 group by 的一些细节

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

3.1 group by后的列

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

select count(*)

from emp

group by deptno

复制代码

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

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

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

select deptno,job,count(*)

from emp

group by deptno,job

order by deptno,job

#为了增强可读性我们加上order by

复制代码

3.2 group by后面的having

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

针对问题：统计每个部门有多少人，只显示超过4个人的部门及人数

如果我们用where来限定这个数据，会得到如下提示：

select deptno,count(*)

from emp

group by deptno

having count(*)>4

复制代码

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

select deptno,count(*)

from emp

having count(*)>4

group by deptno

复制代码

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

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

select deptno,job,count(*)

from emp

group by deptno,job

having count(*)>3

and job not like 'MANAGER'

复制代码

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

having后面跟的聚合数据一定要在select中出现吗？不是的！

比如我们要求：部门人数超过4个的平均工资信息

select deptno,avg(sal)

from emp

group by deptno

having count(*)>4

复制代码

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的顺序可以互换