Having和Where那点事

今天的话题稍微简单些，算是SQL扫盲吧。

我们今天主要说一说having和where那点事。

1.数据准备

DROP table Student;

CREATE TABLE Student

(StuName VARCHAR2(10) NOT NULL,

 Subject VARCHAR2(10),

 Score INTEGER);

 

insert into Student values('A1','数学',80);

insert into Student values('A1','英语',90);

insert into Student values('A1','语文',80);

insert into Student values('A2','英语',40);

insert into Student values('A2','数学',60);

insert into Student values('A2','语文',80);

insert into Student values('A3','英语',80);

insert into Student values('A3','数学',100);

insert into Student values('A3','语文',70);

commit;

2.SQL写法

假设我们要求平均分大于80 的学生姓名和平均分，写法如下

select s.StuName, round(avg(s.score)) as avg_score

from Student s

group by s.StuName

having avg(s.score)>80;

把having改成where该怎么写呢？

with tmp as

(select s.StuName, round(avg(s.score)) as avg_score

from Student s

group by s.StuName)

select *

from tmp

where avg_score>80;

上面两种写法都超级简单，但是可以帮我们理解分组和having筛选的问题。

也就是说，group by后，按照group by的条件，group by条件之外的项目都是聚合，组成一个group by条件为唯一键的新结果集；having对这个结果集进行进一步筛选，类似where筛选，但是与where不同的是，可以利用聚合进行筛选（我已经语无伦次了，其实聚合之后，多条数据成为一条数据，having就是对这些数据进行筛选）。

那么，having可以对非聚合项目进行筛选吗？

答案是可以，但是只能对group by条件进行。

select s.StuName, round(avg(s.score)) as avg_score

from Student s

group by s.StuName

having avg(s.score)>80

   and s.STUNAME='A1';

虽然可以，但是我们不推荐这么做，非聚合项目的筛选应该放在where里面，因为where先执行，先筛选，后聚合，可以减少聚合的记录数，提高性能。

我们试一下用非group by进行having筛选；

select s.StuName, round(avg(s.score)) as avg_score

from Student s

group by s.StuName

having s.Subject in ('语文','数学');

出错了，错误是 ORA-00979: 不是 GROUP BY 表达式。

我们结合这个例子再次讨论having的本质；

Having其实就是筛选，计算方式和where一样，不过筛选的时机不一样，having要在聚合之后。既然和where一样，那么，这种筛选也是行级别进行的，当然，是针对分组和聚合之后组成的行级结果集。

上我们强调了【后】这个字，再次强调一遍，having是对分组和聚合之后的结果集进行筛选，上面出错的的原因也很明白了，因为分组之后的结果集是分组条件和聚合结果组成，再次用非分组条件，并且没有进行聚合运算筛选，分组之后的结果集中已经没有这个字段了，当然会报错。

截止到目前，我们得出两个结论：

①分组之后用分组条件和聚合结果组成新的结果集，没有聚合的非分组条件如果已经无法识别了（语无伦次，不太严谨）；

②对新的结果集进行having筛选和where原理一样，就是对①的结果集进行“where”筛选。