【mysql】group by、on、where、having的常见问题

àì夳堔傛蜴生んèń 2022-09-12 05:53 150阅读 0赞

### group by、on、where、having的常见问题 ###

*  sql 执行顺序
 *  group by
 *  where 和 having
 *  on 和 where

# sql 执行顺序 #

1）SQL的书写顺序

`SELECT- DISTINCT- FROM- JOIN ON- WHERE- GROUP BY- HIVING- ORDER BY- LIMIT`

2）真正执行的顺序：  
随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：

`FROM- ON- JOIN- WHERE- GROUP BY- HAVING- SELECT- DISTINCT- ORDER BY- LIMIT`

3）外连接  
外连接确定主从表：左外连左主右从，右外连右主左从！

外连接的结果集：主表取所有，从表取匹配，主表与从表未匹配的数据通过null来补全.

# group by #

先来看一下表1，表名为`test`：  
![在这里插入图片描述][d3f8a77d8917477abd8853a9f3db3fab.png]  
执行如下SQL语句：`SELECT name FROM test GROUP BY name`；

你应该很容易知道运行的结果，没错，就是下表2：  
![在这里插入图片描述][e9f0aef9a7294da2b1a916fe98cffdef.png]  
可是为了能够更好的理解“`group by`”多个列和”`聚合函数`“的应用，建议在思考的过程中，由表1到表2的过程中，增加一个虚构的中间表：虚拟表3。

下面说说如何来思考上面SQL语句执行情况：

1.  `FROM test`：该句执行后，应该结果和表1一样，就是原来的表。
2.  `FROM test Group BY name`：该句执行后，我们想象生成了虚拟表3，

如下所图所示，

生成过程是这样的：`group by name`，那么找name那一列，具有相同name值的行，合并成一行，如对于name值为aa的，那么<1 aa 2>与<2 aa 3>两行合并成1行，所有的 id 值和 number 值写到一个单元格里面。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_13_color_FFFFFF_t_70_g_se_x_16]  
接下来就要针对虚拟表3执行`Select`语句了：

（1）如果执行`select *`的话，那么返回的结果应该是虚拟表3，可是id和number中有的单元格里面的内容是多个值的，而关系数据库就是基于关系的，单元格中是不允许有多个值的，所以执行`select * 语句`就报错了。

这就解答select报错问题： ERROR 1055 (42000)

> ERROR 1055 (42000): Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘sqltest.score.sid’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by

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

（3）那么对于`id`和`number`里面的单元格有多个数据的情况怎么办呢？

*  答案就是用聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如`count(id)`，`sum(number)`，而每个聚合函数的输入就是每一个多数据的单元格。

（4）例如我们执行`select name,sum(number) from test group by name`，那么 sum 就对虚拟表3的 number列 的每个单元格进行 sum 操作，例如对 name 为 aa 的那一行的 number列 执行 sum 操作，即2+3，返回5，最后执行结果如下：  
![加粗样式][196cf9fe1aa047cdb559c521a68fcd65.png]  
（5）group by 多个字段该怎么理解呢：

如`group by name,number`，可以把name和number 看成一个整体字段，以他们整体来进行分组的。如下图  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_15_color_FFFFFF_t_70_g_se_x_16]  
（6）接下来就可以配合select和聚合函数进行操作了。如执行`select name,sum(id) from test group by name,number`，结果如下图：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_10_color_FFFFFF_t_70_g_se_x_16]

# where 和 having #

where：针对表中的列，一定是表中存在的字段，才可以进行过滤

having：针对查询结果中的列，即select后面的字段

举个栗子：  
一、显示每个地区的总人口数和总面积：

SELECT region, SUM(population), SUM(area) 
    FROM bbc 
    GROUP BY region

先以`region`把返回记录分成多个组，这就是`GROUP BY`的字面含义。分完组后，然后用聚合函数对每组中的不同字段（一或多条记录）作运算。

二、 显示每个地区的总人口数和总面积．仅显示那些面积超过1000的地区。

SELECT region, SUM(population), SUM(area) 
    FROM bbc 
    GROUP BY region 
    HAVING SUM(area)>1000

在这里，不能用`wher`e来筛选超过1000的地区，因为表中不存在这样一条记录。 相反，`having`子句可以让我们筛选成组后的各组数据。

如果一条sql使用了 group by 那么 select 后面可以显示的字段可以有：

*  group by 的字段(多个)
 *  聚合函数， 函数体中字段任意，只要是表中的字段就行，不需要非得是group by 后面的字段

如果需要对分组后的每一组数据做细化的筛选，那么可以在group by 后面接having() 函数，`having函数体多为聚合函数过滤` 。

注意：

`group by` 分组后，只能显示合法数据，一般都是每一组中的其中一条，违反这个规则， 一定会报语法错误。

`having` 是对分组（group by）后的筛选条件，对分组后的数据进行筛选，作用于每个组，限制的是组，而不是行。

WHERE过滤行，HAVING过滤组

当同时含有 `where` 子句、`group by` 子句 、`having` 子句、`聚集函数`时，执行顺序如下：

1.  先执行 where 子句，查找（过滤出）表中符合条件的数据；
2.  再 group by 子句，对where查找出的数据进行分组；
3.  对 group by 子句形成的组，运行聚集函数计算每一组的值；
4.  最后用 having 子句，查找（过滤出）符合条件的组

# on 和 where #

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用`left join`时，`on`和`where`条件的区别如下：

1、 on条件是在生成临时表时使用的条件，主要是对从表进行过滤，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab2
    id size
    1   10
    2   20
    3   30
    
    表2：tab2 
    size name
    10   AAA
    20   BBB
    20   CCC

sql语句一：

select * 
    from tab1 
    left join tab2 
    on (tab1.size = tab2.size) where tab2.name='AAA'
    
    过程：
    1、中间表on条件： tab1.size = tab2.size 
    
    	1 10 10 AAA
    	2 20 20 BBB
    	2 20 20 CCC
    	3 30 (null) (null)
    
    2、再对中间表过滤where 条件：tab2.name='AAA' 
    
    	1 10 10 AAA

sql语句二：

select * form tab1 
    left join tab2 
    on (tab1.size = tab2.size and tab2.name=’AAA’)
    
    过程：
    1、中间表on条件：tab1.size = tab2.size and tab2.name='AAA'(条件不为真也会返回左表中的记录) 
    
    1 10 10 AAA
    2 20 (null) (null)
    3 30 (null) (null)

其实以上结果的关键原因就是`left join`、`right join`、`full join`的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。

而`inner join`没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。on为了反映外连接中一方的全连接，而where没有这个功能，内连接配对是可以的。

[d3f8a77d8917477abd8853a9f3db3fab.png]: /images/20220828/117fd68aa9754be3a30fcedc1f0104d0.png
[e9f0aef9a7294da2b1a916fe98cffdef.png]: /images/20220828/d00898b5474c4753ad9b7b9cd72aaf69.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_13_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/898997e29ca7421c96cacc9ab3ab984c.png
[196cf9fe1aa047cdb559c521a68fcd65.png]: /images/20220828/072d34c60e15452e96c661e075f9f4ae.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_15_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/cf276fbcd5084f1091ca7a2e6fdfce47.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_10_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/08b6d798594b4bc2a3a9d8c43cfdd74f.png