SQL学习--group by

学习环境:oracle中的模拟表,使用hr模式连接

1. group by子句:

在select列表中不是分组行数的所有项必定是group by 子句的分组特性

 

select  d.DEPARTMENT_NAME,max(e.SALARY)

from EMPLOYEES e

join DEPARTMENTS d

on e.DEPARTMENT_ID=d.DEPARTMENT_ID

group by  d.DEPARTMENT_NAME;

这个是正确的

select  d.DEPARTMENT_NAME,max(e.SALARY)

from EMPLOYEES e

join DEPARTMENTS d

on e.DEPARTMENT_ID=d.DEPARTMENT_ID

group by  e.DEPARTMENT_ID;

这个是错误的,因为此处select中所包含的行并没有被group所使用,此处会提示

ORA-00979: 不是 GROUP BY 表达式

00979. 00000 -  "not a GROUP BY expression"

注意一个重点: 在select中不是分组函数的所有项必定全部是group by 的分组特性

select  d.DEPARTMENT_ID,d.DEPARTMENT_NAME,max(e.SALARY)

from EMPLOYEES e 

join DEPARTMENTS d

on e.DEPARTMENT_ID=d.DEPARTMENT_ID

group by  d.DEPARTMENT_ID,d.DEPARTMENT_NAME;

就像这个一样,虽然 d.DEPARTMENT_ID,d.DEPARTMENT_NAME这两个其实是一个意思,但是必须还是需要在group by 子句中加上这两个分组特性(PS:我记得MySQL中的group by没那么多事儿啊...)

2. group by 的having限制:

select  d.DEPARTMENT_ID,d.DEPARTMENT_NAME,max(e.SALARY)

from EMPLOYEES e 

join DEPARTMENTS d

on e.DEPARTMENT_ID=d.DEPARTMENT_ID

group by  d.DEPARTMENT_ID,d.DEPARTMENT_NAME

having  d.DEPARTMENT_ID=100;

注意这里:假如having子句使用了非select条目的约束,那么一样不行

select  d.DEPARTMENT_ID,d.DEPARTMENT_NAME,max(e.SALARY)

from EMPLOYEES e 

join DEPARTMENTS d

on e.DEPARTMENT_ID=d.DEPARTMENT_ID

group by d.DEPARTMENT_ID,d.DEPARTMENT_NAME

having  e.DEPARTMENT_ID=100;

例如这种的,

3. group by 的多列分组特性:

就是在group by 列1,[列2],[列3]...,典型的例子就是查找学生表中各班男女的数目

select d.DEPARTMENT_NAME,j.JOB_TITLE,e.LAST_NAME

from EMPLOYEES e

join DEPARTMENTS d

on e.DEPARTMENT_ID=d.DEPARTMENT_ID

join JOBS j

on e.JOB_ID=j.JOB_ID

group by d.DEPARTMENT_NAME,j.JOB_TITLE,e.LAST_NAME

order by d.DEPARTMENT_NAME,j.JOB_TITLE;

查找每个部门中的每个职位的负责人,

4. 对于分组函数来说:只能嵌套两层,但是单行函数能够多层嵌套

(但是平常也没人会这么用:max(sum(avg(salary))):要是有人这么用,那该喂他吃药了)

分组函数:max,min,avg,sum,count等等,就是把多个值计算为一个值的函数

单行函数:例如大小写,concat,等等,对一行数据进行处理的函数

max和min函数只能作用于number,date,char,varchar2的数据类型

5. having

having与where的显而易见的区别是:

having对group by是具有依赖的,它只能被group的子句所指定,因为having在限制组之前这些行组必须存在

where不能在组中进行过滤操作

再通俗点:where过滤完了之后才能再给group by,然后group by 分组完了之后再使用having对已经分完的组进行再次过滤

看到了没?效果是一样的,根据具体场景灵活运用才对

但是在实际上的优化过程中,假如列上有索引并且该列非常适合走索引的话,还是推荐先where再分组,因为having是对所有的数据全部取出之后再进行的过滤,是会进行全表扫描的(我们对优化也要关心一下)

总结的group by使用方法:

1. 行必须具有共同的特性才能够将其分到一组

2. group注意在where语句之后,在order by 之前

3. select 列表上,假如不是分组函数的,那么必定是分组的特性,select后面跟的只要不是分组函数,那么必须出现在group by里面,这个也是许多初学者碰到错误不明白的

4. 注意where子句中不能有分组函数,例如select * from t1 where id=max(id);

    (00934"group function is not allowed here")这种碰到了直接打死

5. 在oracle中,能够将数据集进行多次分组