• 数据库系统原理与应用教程（046）—— MySQL 查询（八）：分组查询（GROUP BY）

    数据库系统原理与应用教程（046）—— MySQL 查询（八）：分组查询（GROUP BY）

    目录

    使用 GROUP BY 关键字可以将查询结果按照一个或多个列或者表达式进行分组，分组的依据为 GROUP BY 后面的列名或表达式。GROUP BY 通常与聚合函数合用。

    一、GROUP BY 的用法

    GROUP BY 子句的语法格式如下：

    GROUP BY <列名|表达式>[,...] [HAVING 条件表达式] [WITH ROLLUP]

/*
说明：
（1）使用分组查询时，select 后面的字段列表只能包含 GROUP BY 后面的列名或表达式以及聚合函数，不能包含其他的列或表达式，否则会报错。
（2）列名|表达式：分组依据，按列名或表达式进行分组。
（3）HAVING 条件表达式：对分组进行选择，符合条件表达式的结果才会显示。
（4）WITH ROLLUP：在所有记录的最后加上一条记录，该记录为对所有行的统计结果。
（5）可以使用 GROUP_CONCAT() 函数把某个字段中的所有值连接成一个字符串。
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    例如：

    （1）查询每个地址对应的学生人数。

    mysql> select addr,count(*) from student group by addr;
+-----------+----------+
| addr      | count(*) |
+-----------+----------+
| 信阳市    |        3 |
| 开封市    |        3 |
| 新乡市    |        2 |
| 郑州市    |        6 |
+-----------+----------+
4 rows in set (0.00 sec)

-- 该查询包含的列 s_name 既不属于 group by 又不在聚合函数中，因此出现错误。
mysql> select s_name,addr,count(*) from student group by addr;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.student.s_name' which is not functionally dependent on co
lumns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    （2）查询每个地址对应的学生名单。

    mysql> select addr,group_concat(s_name) from student group by addr;
+-----------+----------------------------------------------------------+
| addr      | group_concat(s_name)                                     |
+-----------+----------------------------------------------------------+
| 信阳市    | 张晓刚,刘岩,奥巴马                                       |
| 开封市    | 刘若非,董雯花,周健华                                     |
| 新乡市    | 刘小青,特朗普                                            |
| 郑州市    | 曹梦德,刘艳,周华建,张学有,李明博,达芬奇                  |
+-----------+----------------------------------------------------------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    二、使用表达式分组

    例如：以手机号前三位分组，查询每组对应的学生人数。

    mysql> select left(phone,3) phone3,count(*) from student group by 1;
+--------+----------+
| phone3 | count(*) |
+--------+----------+
| 130    |        1 |
| 131    |        1 |
| 132    |        1 |
| 133    |        1 |
| 135    |        1 |
| 136    |        4 |
| 137    |        2 |
| 138    |        3 |
+--------+----------+
8 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    三、使用多个列分组

    例如：按照 addr 和 gender 分组，查询每组的记录数。

    mysql> select * from student;
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2011 | 张晓刚    | 男     | 1999-12-03 00:00:00 | 13163735775 | 信阳市    |
| S2012 | 刘小青    | 女     | 1999-10-11 00:00:00 | 13603732255 | 新乡市    |
| S2013 | 曹梦德    | 男     | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2014 | 刘艳      | 女     | 1998-06-24 00:00:00 | 13623735335 | 郑州市    |
| S2015 | 刘岩      | 女     | 1999-07-06 00:00:00 | 13813735225 | 信阳市    |
| S2016 | 刘若非    | 女     | 2000-08-31 00:00:00 | 13683735533 | 开封市    |
| S2021 | 董雯花    | 女     | 2000-07-30 00:00:00 | 13533735564 | 开封市    |
| S2022 | 周华建    | 男     | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2023 | 特朗普    | 男     | 1999-06-21 00:00:00 | 13343735588 | 新乡市    |
| S2024 | 奥巴马    | 男     | 2000-10-17 00:00:00 | 13843735885 | 信阳市    |
| S2025 | 周健华    | 男     | 2000-08-22 00:00:00 | 13788736655 | 开封市    |
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
| S2031 | 李明博    | 女     | 1999-10-26 00:00:00 | 13643732222 | 郑州市    |
| S2032 | 达芬奇    | 男     | 1999-12-31 00:00:00 | 13043731234 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
14 rows in set (0.00 sec)

mysql> select addr,gender,count(*) from student group by 1,2;
+-----------+--------+----------+
| addr      | gender | count(*) |
+-----------+--------+----------+
| 信阳市    | 女     |        1 |
| 信阳市    | 男     |        2 |
| 开封市    | 女     |        2 |
| 开封市    | 男     |        1 |
| 新乡市    | 女     |        1 |
| 新乡市    | 男     |        1 |
| 郑州市    | 女     |        2 |
| 郑州市    | 男     |        4 |
+-----------+--------+----------+
8 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    四、分组时使用 WHERE 子句

    分组时如果使用了 WHERE 子句，则先使用 WHERE 对表中的数据进行筛选，然后进行分组和统计。

    例如：查询学生表每个地址对应的【男生】数量。

    mysql> select addr,count(*) from student where gender = '男' group by addr;
+-----------+----------+
| addr      | count(*) |
+-----------+----------+
| 信阳市    |        2 |
| 开封市    |        1 |
| 新乡市    |        1 |
| 郑州市    |        4 |
+-----------+----------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    五、使用 HAVING 对分组进行选择

    使用 HAVING 子句可以对分组进行选择。当 HAVING 子句与 WHER 子句同时使用时，查询执行的顺序为：先使用 WHERE 对表中的记录进行筛选，然后对满足条件的记录分组与统计，再使用 HAVING 子句对分组进行选择。

    例如：

    （1）按照 addr 分组，查询每个地址对应的人数。

    mysql> select addr,count(*) from student group by addr;
+-----------+----------+
| addr      | count(*) |
+-----------+----------+
| 信阳市    |        3 |
| 开封市    |        3 |
| 新乡市    |        2 |
| 郑州市    |        6 |
+-----------+----------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    （2）按照 addr 分组，查询每个地址中人数超过 3 人的组。

    mysql> select addr,count(*) from student group by addr having count(*)>3;
+-----------+----------+
| addr      | count(*) |
+-----------+----------+
| 郑州市    |        6 |
+-----------+----------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    （3）按照 addr 分组，查询每个地址中男生人数超过 3 人的组。

    mysql> select addr,count(*) from student where gender = '男' 
    -> group by addr having count(*)>3;
+-----------+----------+
| addr      | count(*) |
+-----------+----------+
| 郑州市    |        4 |
+-----------+----------+
1 row in set (0.00 sec)

-- 该查询的执行过程如下：
-- 1、筛选性别为【男】的学生记录
mysql> select * from student where gender = '男';
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2011 | 张晓刚    | 男     | 1999-12-03 00:00:00 | 13163735775 | 信阳市    |
| S2013 | 曹梦德    | 男     | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2022 | 周华建    | 男     | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2023 | 特朗普    | 男     | 1999-06-21 00:00:00 | 13343735588 | 新乡市    |
| S2024 | 奥巴马    | 男     | 2000-10-17 00:00:00 | 13843735885 | 信阳市    |
| S2025 | 周健华    | 男     | 2000-08-22 00:00:00 | 13788736655 | 开封市    |
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
| S2032 | 达芬奇    | 男     | 1999-12-31 00:00:00 | 13043731234 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
8 rows in set (0.00 sec)

-- 2、针对 addr 分组
mysql> select addr,count(*) from student where gender = '男' group by addr;
+-----------+----------+
| addr      | count(*) |
+-----------+----------+
| 信阳市    |        2 |
| 开封市    |        1 |
| 新乡市    |        1 |
| 郑州市    |        4 |
+-----------+----------+
4 rows in set (0.00 sec)

-- 3、对分组进行筛选
mysql> select addr,count(*) from student 
       where gender = '男' group by addr having count(*)>3;
+-----------+----------+
| addr      | count(*) |
+-----------+----------+
| 郑州市    |        4 |
+-----------+----------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47

    六、WITH ROLLUP 参数

    在所有记录的最后加上一条记录，该记录为对所有行的统计结果。

    例如：按照 addr 分组，查询每个地址对应的人数。

    mysql> select addr,count(*) from student group by addr with rollup;
+-----------+----------+
| addr      | count(*) |
+-----------+----------+
| 信阳市    |        3 |
| 开封市    |        3 |
| 新乡市    |        2 |
| 郑州市    |        6 |
| NULL      |       14 |
+-----------+----------+
5 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
  • 相关阅读:
    CNN卷积神经网络之卷积核及其实现示例
    【python】带你采集基金股票数据并作可视化操作
    java-各种成员变量初始化过程-待完善
    二、SpringBoot自动装配及SPI的理解
    论文阅读：One Embedder, Any Task: Instruction-Finetuned Text Embeddings
    面向生产的 LLM 优化
    Wayland introduce
    市场调研实业怎样使用自动化程序自动识别信息
    WPF_布局基础
    paddle2.0简要安装过程
  • 原文地址：https://blog.csdn.net/weixin_44377973/article/details/125911688