【Mysql】7. 聚合函数, 分组select

最新推荐文章于 2024-08-16 23:11:30 发布

派大星的菠萝派

最新推荐文章于 2024-08-16 23:11:30 发布

阅读量326

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/hephaestushao/article/details/88057078

本文详细介绍了SQL中的聚合函数如AVG、COUNT、MAX、MIN和SUM的使用方法，以及如何通过GROUP BY和HAVING子句进行数据分组和过滤。包括处理NULL值的技巧，以及WHERE和HAVING子句的区别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.聚合函数(组函数)

在一个行的集合（一组行）上进行操作，对每个组给一个结果。

AVG([distinct] expr)

求平均值

COUNT({*|[distinct] } expr)

统计行的数量

MAX([distinct] expr)

求最大值

MIN([distinct] expr)

求最小值

SUM([distinct] expr)

求累加和

例子:

mysql> select * from salary_tab; //表记录

+--------+---------+

| userid | salary |

+--------+---------+

| 1 | 1000.00 |

| 2 | 2000.00 |

| 3 | 3000.00 |

| 4 | NULL |

| 5 | 1000.00 |

+--------+---------+

rows in set (0.00 sec)

1.count

mysql> select count(*) from salary_tab where salary='1000';

+----------+

| count(*) |

+----------+

| 2 |

+----------+

mysql> select count(*) from salary_tab;　　#没有条件，默认统计表数据行数

+----------+

| count(*) |

+----------+

| 5 |

+----------+

2.max/min

mysql> select max(salary) from salary_tab;

+-------------+

| max(salary) |

+-------------+

| 3000.00 |

+-------------+

mysql> select min(salary) from salary_tab;

+-------------+

| min(salary) |

+-------------+

| 1000.00 |

+-------------+

3.sum/avg

mysql> select sum(salary) from salary_tab;

+-------------+

| sum(salary) |

+-------------+

| 7000.00 |

+-------------+

mysql> select avg(salary) from salary_tab; //过滤掉记录为null的行,不参与平均数的计数中,7000/4

+-------------+

| avg(salary) |

+-------------+

| 1750.000000 |

+-------------+

mysql> select avg(ifnull(salary,0)) from salary_tab; //意思:将记录为null的行,对应列salary的值为0,结果就为7000/5

+-----------------------+

| avg(ifnull(salary,0)) |

+-----------------------+

| 1400.000000 |

+-----------------------+

注意：要想列值为NULL的行也参与组函数的计算，必须使用IFNULL函数对NULL值做转换。

2.分组select

分组SELECT的基本格式：

　　select [聚合函数] 字段名 from 表名

　　　　[where 查询条件]

　　　　[group by 字段名]

　　　　[having 过滤条件]

2.1 group by

1）指定一个列进行分组

mysql> select salary,count(*) from salary_tab

-> where salary>=2000

-> group by salary;

+---------+----------+

| salary | count(*) |

+---------+----------+

| 2000.00 | 1 |

| 3000.00 | 1 |

+---------+----------+

2）指定多个分组列，‘大组中再分小组’

mysql> select userid,count(salary) from salary_tab

-> where salary>=2000

-> group by salary,userid;

+--------+---------------+

| userid | count(salary) |

+--------+---------------+

| 2 | 1 |

| 3 | 1 |

+--------+---------------+

2.2 having

对分组结果进行过滤

注意：

　　不能使用WHERE子句对分组后的结果进行过滤

　　不能在WHERE子句中使用组函数，仅用于过滤行

mysql> select playerno

-> from PENALTIES

-> where count(*)>1

-> group by playerno;

ERROR 1111 (HY000): Invalid use of group function

------

因为WHERE子句比GROUP BY先执行，而组函数必须在分完组之后才执行，且分完组后必须使用having子句进行结果集的过滤。

！！！having子语句与where子语句区别：

　　where子句在分组前对记录进行过滤；

　　having子句在分组后对记录进行过滤

------

mysql> select salary,count(*) from salary_tab

-> where salary>=2000

-> group by salary

-> having count(*)>=0;

+---------+----------+

| salary | count(*) |

+---------+----------+

| 2000.00 | 1 |

| 3000.00 | 1 |

+---------+----------+

1）HAVING可以单独使用而不和GROUP BY配合,如果只有HAVING子句而没有GROUP BY，表中所有的行分为一组

2）HAVING子句中可以使用组函数

3）HAVING子句中的列，要么出现在一个组函数中，要么出现在GROUP BY子句中(否则出错)