MySQL中group by子句与having实例分析

group By语句从英文的字面意义上理解就是"根据(by)一定的规则进行分组(Group)"。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。

---

【1】语法格式与聚合函数

① group by语法

select [columns] from table_name [where..] group by [columns] [having ...]

在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。

包含在GROUP BY 子句中的列不必包含在SELECT 列表中：

SELECT AVG(salary) FROM employeesGROUP BY department_id ;

在GROUP BY子句中包含多个列：

SELECT department_id dept_id, job_id, SUM(salary)
FROM employeesGROUP BY department_id, job_id ;

② 非法使用组函数

不能在WHERE 子句中使用组函数，可以在HAVING 子句中使用组函数。

SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;

ORA-00934: group function is not allowed here

WHERE 子句中不能使用组函数

---

③ 常见的聚合函数

常见的聚合函数如下表所示：

函数	作用
sum	求和
avg	求平均值
max	求最大值
min	求最小值
first	第一条记录，仅Access支持
last	最后一条记录，仅Access支持
count	统计记录数

count里面可以使用两种参数：* 代表统计记录，字段名代表统计对应的字段(NULL不统计)。

需要注意的是，count统计记录数，将会只返回一条记录，如下示例：

SELECT
	goods_type,price,COUNT(goods_type)
FROM
	tb_goods 

查询结果如下：

---

tb_goods表数据如下：

---

【2】Having与Where的区别

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据。where条件中不能包含聚合函数，可以使用where条件过滤出特定的行。

Having字句与where子句一样可以进行条件判断的，另外Having 子句通常用来筛选满足条件的组，即在分组之后过滤数据。条件中经常包含聚合函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

通常使用group by+having的时候会使用聚合函数，因为分组之后的列要么是聚合函数，要么是group by( 列)中的列。

having后面要么跟聚合函数，要么跟select中的列。

group by 存在时，select中除了聚集函数外，所有的基本列必须是group by里面存在的。having同group by一起使用时，having过滤group by 分组后的数据，一般通过select语句里面的聚合函数进行过滤。

---

只使用group by

select goods_type,COUNT(goods_type) from tb_goods GROUP BY goods_type

查询结果如下：

---

使用having子句

SELECT
	goods_type,price
FROM
	tb_goods HAVING price>50

查询结果如下：

---

使用group by+having

SELECT
	goods_type,
	COUNT(goods_type)
FROM
	tb_goods
GROUP BY
	goods_type
HAVING
	avg(price) > 100 //这里使用聚合函数

查询结果如下：

---

【3】项目实例

从图片表中查出拥有1个以上机房的站址。

注意图片表，machine_room_id 不唯一，因为值为0时可能对应多个站址；site_id更不唯一了，因为肯定有多条记录对应同一个site_id。

思路如下：首先根据machine_room_id，site_id进行分组查询，然后从查询结果中根据site_id进行分组，然后查找count(site_id)>1 的site_id。

解释如下：首先进行联合分组查询，这样留下来的就是site_id +machine_room_id不重复的数据；再对site_id进行分组，查询记录数>1的site_id。请注意，题目要求是从图片表中查出拥有1个以上机房的站址。

code1:

select site_id,count(*) from (

-- code11
select site_id,machine_room_id  from tb_device_images 
where device_type='machineroom'  
group by machine_room_id ,site_id
-- code11

)t group by site_id HAVING count(*)>1;

code2:

select site_id,count(*) from (
-- code22
select site_id,machine_room_id  from tb_device_images 
where device_type='machineroom'  
group by machine_room_id // 注意区别在这里
-- code22
)t group by site_id HAVING count(*)>1;

对比 code1 和code2查询结果：

• code1有41条结果，code2有40条结果；红色字体表示差异所在。

那么是什么导致了差异呢？

我们看code11 和code22对比：

• code22进行分组时只根据machine_room_id，这样就导致数据丢失，分析如下。

根据machine_room_id进行分组时 ,同一数据的记录只会保留一条。那么如图所示红色站址都是丢失的数据。

这里可能有疑问，为什么最后结果只差一条？这是因为11条红色site_id中，只有一条记录对应的site_id还有额外一条记录在下方，故HAVING count(*)>1查询时，其他十条都被过滤掉了。如果不加这个条件，那么差异将是10！！！

在此，也证明了group by定义规则的正确性，当你根据某列进行分组查询多列时，如该项目实例。如果查询多列，那么一定要保证查询列要么为聚合函数要么在分组条件中。

理想情况下，一个site_id对应一个_machine_room_id，且machine_room_id为unique，那么可以只根据machine_room_id进行分组。然后在从该次查询结果中 查找拥有一个以上机房的站址。

综上，根据分组查询时，要注意题目要求，sql中select后的参数，group by 条件参数，是否唯一，是否有having 要求等综合分析。

---

【4】GROUP_CONCAT(expr)

查看分组之后每组属性值列表。

如按照性别分组，分别查看男性和女性中的名字。

select GROUP_CONCAT(name) ,sex from p_user GROUP BY sex

另外，GROUP_CONCT也可用来进列转行操作：

select GROUP_CONCAT(DISTINCT brand_name) FROM goods_sale
where leaguer_code='13007509648'