首页 > 编程笔记 > MySQL笔记 阅读：18

MySQL GROUP BY子句的用法（非常详细）

分组查询是对数据按照某个或多个字段进行分组。MySQL 中使用 GROUP BY 关键字对数据进行分组，语法格式如下： 
[GROUP BY  字段] [HAVING <条件表达式>]

为了方便演示 GROUP BY 的用法，需要往数据表插入如下数据： 
mysql> INSERT INTO fruits (f_id, s_id, f_name, f_price)
       VALUES('a1', 101,'apple',5.2),
       ('b1',101,'blackberry', 10.2),
       ('bs1',102,'orange', 11.2),
       ('bs2',105,'melon',8.2),
       ('t1',102,'banana', 10.3),
       ('t2',102,'grape', 5.3),
       ('o2',103,'coconut', 9.2),
       ('c0',101,'cherry', 3.2),
       ('a2',103, 'apricot',2.2),
       ('l2',104,'lemon', 6.4),
       ('b2',104,'berry', 7.6),
       ('m1',106,'mango', 15.7),
       ('m2',105,'xbabay', 2.6),
       ('t4',107,'xbababa', 3.6),
       ('m3',105,'xxtt', 11.6),
       ('b5',107,'xxxx', 3.6);

MySQL创建分组

GROUP BY 关键字通常和集合函数一起使用，比如 MAX()、MIN()、COUNT()、SUM()、AVG()。

例如，要返回每个水果供应商提供的水果种类，就要在分组过程中用到 COUNT() 函数，把数据分为多个逻辑组，并对每个组进行集合计算。

【实例 1】根据 s_id 对表 fruits 中的数据进行分组，SQL 语句如下： 
SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;
查询结果如下： 
+------+-------+
| s_id | Total |
+------+-------+
| 101  |    3  |
| 103  |    2  |
| 104  |    2  |
| 107  |    2  |
| 102  |    3  |
| 105  |    3  |
| 106  |    1  |
+------+-------+

GROUP BY 子句按照 s_id 升序排序并对数据分组，可以看到 s_id 为 101、102、105 的供应商都提供 3 种水果，s_id 为 103、104、107 的供应商都提供 2 种水果，s_id 为 106 的供应商只提供 1 种水果。

如果要查看每个供应商提供的水果的种类，该怎么办呢？在 MySQL 中，可以在 GROUP BY 子句中使用 GROUP_CONCAT() 函数，将每个分组中的各个字段的值显示出来。

【实例】根据 s_id 对表 fruits 中的数据进行分组，将每个供应商提供的水果名称显示出来，SQL 语句如下： 
SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id;
查询结果如下： 
+------+-------------------------+
| s_id | Names                   |
+------+-------------------------+
|  101 | apple,blackberry,cherry |
|  102 | orange,banana,grape     |
|  103 | apricot,coconut         |
|  104 | berry,lemon             |
|  105 | melon,xbabay,xxtt       |
|  106 | mango                   |
|  107 | xxxx,xbababa            |
+------+-------------------------+
由结果可以看到，GROUP_CONCAT() 函数将每个分组中的名称显示出来了，其名称的个数与 COUNT() 函数计算出来的相同。

MySQL使用HAVING过滤分组

GROUP BY 可以和 HAVING 一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。

【实例 2】根据 s_id 对表 fruits 中的数据进行分组，并显示水果种类大于 1 的分组信息，SQL 语句如下： 
SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id HAVING COUNT(f_name) > 1;
查询结果如下： 
+------+-------------------------+
| s_id | Names                   |
+------+-------------------------+
|  101 | apple,blackberry,cherry |
|  102 | orange,banana,grape     |
|  103 | apricot,coconut         |
|  104 | berry,lemon             |
|  105 | melon,xbabay,xxtt       |
|  107 | xxxx,xbababa            |
+------+-------------------------+
s_id 为 101、102、103、104、105、107 的供应商提供的水果种类大于 1，满足 HAVING 子句条件，因此出现在返回结果中；而 s_id 为 106 的供应商提供的水果种类等于 1，不满足限定条件，因此不在返回结果中。

HAVING 关键字与 WHERE 关键字都是用来过滤数据的，两者有什么区别呢？最重要的区别是，HAVING 在数据分组之后进行过滤来选择分组，而 WHERE 在分组之前选择记录。另外，WHERE 排除的记录不再包括在分组中。

MySQL在GROUP BY子句中使用WITH ROLLUP

使用 WITH ROLLUP 关键字之后，可以在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

【实例 3】根据 s_id 对表 fruits 中的数据进行分组，并显示记录数量，SQL 语句如下： 
SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP;
查询结果如下： 
+------+-------+
| s_id | Total |
+------+-------+
|  101 |   3   |
|  102 |   3   |
|  103 |   2   |
|  104 |   2   |
|  105 |   3   |
|  106 |   1   |
|  107 |   2   |
| NULL |   16  |
+------+-------+
由结果可以看到，通过 GROUP BY 分组之后，在显示结果的最后面新添加了一行，该行 Total 列的值正好是上面所有数值之和。

MySQL多字段分组

使用 GROUP BY 可以对多个字段进行分组，GROUP BY 关键字后面跟需要分组的字段。

MySQL 根据多字段的值来进行层次分组，分组层次从左到右，即先按第 1 个字段分组，然后在第 1 个字段值相同的记录中根据第 2 个字段的值进行分组，以此类推。

【实例 4】根据 s_id 和 f_name 字段对表 fruits 中的数据进行分组，SQL 语句如下： 
mysql> SELECT * FROM fruits GROUP BY s_id,f_name;
查询结果如下： 
+-----+-----+------------+---------+
| f_id| s_id| f_name     | f_price |
+-----+-----+------------+---------+
| a1  | 101 | apple      | 5.20    |
| a2  | 103 | apricot    | 2.20    |
| b1  | 101 | blackberry | 10.20   |
| b2  | 104 | berry      | 7.60    |
| b5  | 107 | xxxxx      | 3.60    |
| bs1 | 102 | orange     | 11.20   |
| bs2 | 105 | melon      | 8.20    |
| c0  | 101 | cherry     | 3.20    |
| l2  | 104 | lemon      | 6.40    |
| m1  | 106 | mango      | 15.70   |
| m2  | 105 | xbabay     | 2.60    |
| m3  | 105 | xxtt       | 11.60   |
| o3  | 103 | coconut    | 9.20    |
| t1  | 102 | banana     | 10.30   |
| t2  | 102 | grape      | 5.30    |
| t4  | 107 | xbababa    | 3.60    |
+-----+-----+------------+---------+
由结果可以看到，查询记录先按照 s_id 进行分组，再对 f_name 字段按不同的取值进行分组。

注意，如果上面的例子报错为 SQL 模式问题，可以在 my.ini 文件中的 sql_mode 中删除 ONLY_FULL_GROP_BY，然后重启 MySQL 服务器即可解决问题。

MySQL GROUP BY和ORDER BY一起使用

某些情况下需要对分组进行排序。在前面的介绍中，ORDER BY 用来对查询的记录排序，它和 GROUP BY 一起使用就可以完成对分组的排序。

为了演示效果，首先创建数据表，SQL 语句如下： 
CREATE TABLE orderitems
(
o_num      int          NOT NULL,
o_item     int          NOT NULL,
f_id       char(10)     NOT NULL,
quantity   int          NOT NULL,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY (o_num,o_item)
) ;

然后插入演示数据，SQL 语句如下： 
INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)
VALUES(30001, 1, 'a1', 10, 5.2),
(30001, 2, 'b2', 3, 7.6),
(30001, 3, 'bs1', 5, 11.2),
(30001, 4, 'bs2', 15, 9.2),
(30002, 1, 'b3', 2, 20.0),
(30003, 1, 'c0', 100, 10),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 10),
(30005, 2, 'b1', 10, 8.99),
(30005, 3, 'a2', 10, 2.2),
(30005, 4, 'm1', 5, 14.99);

【实例 5】查询订单价格大于 100 的订单号和总订单价格，SQL 语句如下： 
SELECT o_num,  SUM(quantity * item_price) AS orderTotal FROM orderitems
GROUP BY o_num HAVING SUM(quantity*item_price) >= 100;
查询结果如下： 
+-------+------------+
| o_num | orderTotal |
+-------+------------+
| 30001 |    268.80  |
| 30003 |   1000.00  |
| 30004 |    125.00  |
| 30005 |    236.85  |
+-------+------------+
从返回的结果中可以看到，orderTotal 列的总订单价格并没有按照一定顺序显示。

接下来使用 ORDER BY 关键字按总订单价格升序显示结果，SQL 语句如下： 
SELECT o_num,  SUM(quantity * item_price) AS orderTotal FROM orderitems
GROUP BY o_num HAVING SUM(quantity*item_price) >= 100 ORDER BY orderTotal;
查询结果如下： 
+-------+------------+
| o_num | orderTotal |
+-------+------------+
| 30004 |    125.00  |
| 30005 |    236.85  |
| 30001 |    268.80  |
| 30003 |   1000.00  |
+-------+------------+
由结果可以看到，GROUP BY 子句按订单号对数据进行分组，SUM() 函数便可以返回总的订单价格，HAVING 子句对分组数据进行过滤，只返回总价格大于 100 的订单，最后使用 ORDER BY 子句升序输出。

注意，当使用 ROLLUP 时，不能同时使用 ORDER BY 子句进行排序，即 ROLLUP 和 ORDER BY 是互相排斥的。

相关文章