理解sql中的group by和having

理解sql中的group by和having 2019-09-16 07:09:00 108人浏览绘本

摘要

下面通过一个例子，来简单解释group by的原理。 1. GROUP BY 假设我们有表1，表名为test：如果我们执行如下sql语句： SELECT name FROM test GROUP BY name 我们很容易可以

下面通过一个例子，来简单解释group by的原理。

1. GROUP BY

假设我们有表1，表名为test：

如果我们执行如下sql语句：

SELECT name FROM test GROUP BY name

我们很容易可以得到运行的结果：

为了能够更好的理解“group by”多个列“和”聚合函数“的应用，这里可以在表1到表2的过程中，引入一个虚构的中间表：虚拟表3。

FROM test Group BY name：该句执行后，我们想象生成了虚拟表3，如下所图所示：

生成过程是这样的：group by name，那么找name那一列，具有相同name值的行，合并成一行，如对于name值为aa的，那么<1 aa 2>与<2 aa 3>两行合并成1行，所有的id值和number值写到一个单元格里面。

接下来再针对虚拟表3执行Select语句：

如果执行select *的话，那么返回的结果应该是虚拟表3，可是id和number中有的单元格里面的内容是多个值的，而关系数据库就是基于关系的，单元格中是不允许有多个值的，所以，执行select * 语句是不允许的。
- 为了约束使用者在编写group by时select多值字段，设计DBMS的开发者也是伤透了脑筋。开发者并不知道将来这个数据库会被用来做什么，所以，他不可能从逻辑上来检查你的select上出现的语句是不是分组属性的一个子集。所以，最简单的方法就是看你的select上出现的属性在group by上出现过。出现过，就通过编译，否则不会。
- Mysql对group by 进行了非ANSI标准的扩展，允许select后含有非group by 的列。所以在mysql中，group by时执行select *不会报错，但也得不到我们想要的数据，只会select出原表中的第一个数据
我们再看name列，每个单元格只有一个数据，所以我们select name的话，就没有问题了。为什么name列每个单元格只有一个值呢，因为我们就是用name列来group by的。
那么对于id和number里面的单元格有多个数据的情况怎么办呢？答案就是用聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如cout(id)，sum(number)，而每个聚合函数的输入就是每一个多数据的单元格。
- 例如我们执行select name,sum(number) from test group by name，那么sum就对虚拟表3的number列的每个单元格进行sum操作，例如对name为aa的那一行的number列执行sum操作，即2+3，返回5，最后执行结果如下：
group by 多个字段该怎么理解呢：如group by name,number，我们可以把name和number 看成一个整体字段，以他们整体来进行分组的。如下图
- 接下来就可以配合select和聚合函数进行操作了。如执行select name,sum(id) from test group by name,number，结果如下图：
- -

2. HAVING

首先，不要错误的认为having必须和group by 配合使用。其实having可以单独使用

having关键字在我们的印象中，貌似和where关键字十分相似，那二者有什么区别呢？

含义：
- “Where”是一个约束声明，在查询数据库的结果返回结果之前对数据库中的查询条件进行约束，即在结果返回之前起作用，且where后面不能使用“聚合函数”；
  - where后面之所以不能使用聚合函数是因为where的执行顺序在聚合函数之前，所以在执行where的时候，还没有结果集，更别说对结果集做聚合了。
- Having”是一个过滤声明，所谓过滤是在查询数据库的结果返回之后进行过滤，即在结果返回之后起作用，并且having后面可以使用“聚合函数”。
  - having既然是对查出来的结果进行过滤，那么就不能对没有select出来的字段使用having，如select id , name from student having score >90;这句话就是错误的。

where和having，一个是起作用在结果返回前，用来过滤记录；一个是起作用在结果返回后，用来过滤结果。这种场景的典型应用如这句：SELECT region,count(school) FROM T02_Bejing_school WHERE region IN ("海淀" , "西城" , "东城") GROUP BY region HAVING count(school) > 10;该句sql可以筛选出北京西城、东城、海淀三个区中学校数量超过10所的区及各区学校数量。即先用where把这三个区的中学过滤出来，然后对结果集做group by，得到一张组合后的虚拟表，最后通过having对虚拟表做二次过滤。

使用的场景：
- 只有WHERE可以使用的场景：
  - 除select外，where还可以用于update、delete和insert into values(select * from table where ..)语句中，having则不行。
  - select语句中，没有select出要被约束的字段的时候，也不可以使用having。就如上文提到的非法语句：select id , name from student having score >90;
- 只有HAVING可以使用的场景：
  - 如果要过滤的字段是原生表中不存在的字段，而是经过聚合函数计算后的字段，那么不可以使用where，只能用having。如：
    - 合法语句：select id , avg(price) as ag from goods group by category having ag > 1000
    - 非法语句：select id , avg(price) as ag from goods where ag group by category > 1000
- 二者都可以使用的场景：
  - 要约束的字段既是原生表的字段，又是sql中被select出来的字段，这时候where和having等效：
    - select price , name from goods where price > 100
    - select price , name from goods having price > 100