关闭

SQL学习--group by

标签: oracleselectsql
165人阅读 评论(0) 收藏 举报
分类:
学习环境: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中,能够将数据集进行多次分组





0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:9155次
    • 积分:421
    • 等级:
    • 排名:千里之外
    • 原创:29篇
    • 转载:2篇
    • 译文:5篇
    • 评论:1条
    文章分类
    最新评论