理解sql中的groupby和having

  • Z时代
  • 2024-01-10
  • 分类：综合

database

下面通过一个例子，来简单解释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语句：

  1. 如果执行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出原表中的第一个数据

  2. 我们再看name列，每个单元格只有一个数据，所以我们select name的话，就没有问题了。为什么name列每个单元格只有一个值呢，因为我们就是用name列来group by的。

  3. 那么对于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，最后执行结果如下：

  4. 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关键字十分相似，那二者有什么区别呢？

  1. 含义：

    • “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对虚拟表做二次过滤。

  1. 使用的场景：

    • 只有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

参考资料

  1. 理解group by

  2. Group by的理解

  3. 正确理解MySQL中的where和having的区别

以上是 理解sql中的groupby和having 的全部内容， 来源链接： utcz.com/z/533494.html

回到顶部