羊村懒王

这个屌丝很懒，什么也没留下！

热门标签

Mysql：分组查询_mysql分组查询

作者：羊村懒王 | 2024-04-11 19:42:56

踩

mysql分组查询

分组查询

1、分组查询是对数据按照某个或多个字段进行分组，在MYSQL中使用GROUP BY关键字对数据进行分组

2、GROUP BY关键字可以将查询结果按照某个字段或多个字段进行分组。字段中值相等的为一组
⑴分组的核心是：在查询SQL中指定分组的列名，然后根据该列的值进行分组，值相等的为一组

3、分组查询的基本的语法格式如下：


 GROUP BY 字段名 [HAVING 条件表达式]
参数：
1、字段名：是指按照该字段的值进行分组(分组是所依据的列名称)
2、HAVING条件表达式：用来限制分组后的显示，符合条件表达式的结果将被显示

创建分组

1、对数据进行分组，一般有两种使用场景：
⑴单独使用GROUP BY关键字，
⑵将GROUP BY关键字与聚合函数一起使用(常用)

2、对于GROUP BY子句的使用，需要注意以下几点：
⑴GROUP BY子句可以包含任意数目的列，使其可以对分组进行嵌套，为数据分组提供更加细致的控制
⑵GROUP BY子句列出的每个列都必须是检索列或有效的表达式，但不能是聚合函数。若在SELECT语句中使用表达式，则必须在GROUP BY子句中指定相同的表达式

3、若用于分组的列中包含有NULL值，则NULL将作为一个单独的分组返回；若该列中存在多个NULL值，则将这些NULL值所在的行分为一组

GROUP BY单独使用

单独使用 GROUP BY关键字时，查询结果会只显示每个分组的第一条记录

例1：无过滤条件时


mysql> SELECT * FROM polls_article;
+----+-----------+----------------+-------+--------+----------------------------+
| id | title     | content        | price | author | create_time                |
+----+-----------+----------------+-------+--------+----------------------------+
|  1 | 三国演义  | 孙悟空大闹天宫 |   132 | 吴承恩 | 2020-09-13 14:48:30.000000 |
|  2 | 三国演义1 | 东汉末年分三国 |   154 | 罗贯中 | 2020-09-13 12:48:30.962070 |
|  3 | 红楼梦    | 刘姥姥进大观园 |   154 | 曹雪芹 | 2020-09-13 12:48:30.962070 |
|  4 | 水浒传    | 逼上梁山       |   140 | 施耐庵 | 2020-09-13 12:48:30.962070 |
|  7 | wed       | dwd            |   123 | 44     | 2020-10-13 17:08:50.493488 |
|  8 | ds        | dwd            |   123 | 32     | 2020-10-13 17:19:18.121264 |
|  9 | 西游记    | 西天取经       |   345 | NULL   | 2020-11-14 15:48:59.000000 |
+----+-----------+----------------+-------+--------+----------------------------+
7 rows in set (0.00 sec)
 
-- 按照price字段对数据进行分组
mysql> SELECT * FROM polls_article GROUP BY price;
+----+-----------+----------------+-------+--------+----------------------------+
| id | title     | content        | price | author | create_time                |
+----+-----------+----------------+-------+--------+----------------------------+
|  7 | wed       | dwd            |   123 | 44     | 2020-10-13 17:08:50.493488 |
|  1 | 三国演义  | 孙悟空大闹天宫 |   132 | 吴承恩 | 2020-09-13 14:48:30.000000 |
|  4 | 水浒传    | 逼上梁山       |   140 | 施耐庵 | 2020-09-13 12:48:30.962070 |
|  2 | 三国演义1 | 东汉末年分三国 |   154 | 罗贯中 | 2020-09-13 12:48:30.962070 |
|  9 | 西游记    | 西天取经       |   345 | NULL   | 2020-11-14 15:48:59.000000 |
+----+-----------+----------------+-------+--------+----------------------------+
5 rows in set (0.00 sec)

注：
1、上面SQL表示：查询表中所有数据(所有列、行)，并对结果按照"price"字段值进行分组
⑴分组："price"字段值相同的为一组
⑵由于是单独使用的GROUP BY关键字，因此只会返回每个分组的第一条记录

例1_1：有过滤条件时


mysql> SELECT id,title,price,author FROM polls_article WHERE price > 130;
+----+----------+-------+--------+
| id | title    | price | author |
+----+----------+-------+--------+
|  1 | 三国演义 |   132 | 吴承恩 |
|  2 | 三国演义 |   154 | 罗贯中 |
|  3 | 水浒传   |   154 | 曹雪芹 |
|  4 | 水浒传   |   140 | 施耐庵 |
|  9 | 西游记   |   345 | NULL   |
+----+----------+-------+--------+
5 rows in set (0.00 sec)
 
mysql> SELECT id,title,price,author FROM polls_article WHERE price > 130 GROUP BY title;
+----+----------+-------+--------+
| id | title    | price | author |
+----+----------+-------+--------+
|  1 | 三国演义 |   132 | 吴承恩 |
|  3 | 水浒传   |   154 | 曹雪芹 |
|  9 | 西游记   |   345 | NULL   |
+----+----------+-------+--------+
3 rows in set (0.00 sec)

注：
1、这个例子中的SQL表示：查询表中"price > 130"的数据(查询的部分列)，在对符合条件的数据哪找"title"字段值进行分组
⑴先找到符合过滤条件的数据，然后再分组

2、前面两个例子中：单独使用GROUP BY关键字只显示每个分组的一条记录
⑴这说明：GROUP BY关键字单独使用时，只能查询出每个分组的一条记录
⑵这样做的意义不大。因此，一般在使用集合函数时才使用GROUP BY关键字

GROUP BY与聚合函数

1、GROUP BY关键字通常与集合函数一起使用。集合函数包括COUNT()函数、SUM()函数、AVG()函数、MAX()函数和MIN()函数

2、如果GROUP BY不与聚合函数一起使用，那么查询结果就是字段取值的分组情况
⑴字段中取值相同的记录为一组，但是只显示该组的第一条记录(跟前面GROUP BY单独使用一样)

3、常用的聚合函数有：
   ⑴COUNT()函数：用于统计记录的条数
   ⑵SUM()函数：用于计算字段的值的总和
   ⑶AVG()函数：用于计算字段的值的平均值
   ⑷MAX()函数：用于查询字段的最大值
   ⑸MIN()函数：用于查询字段的最小值

例2：


mysql> SELECT * FROM polls_article;
+----+----------+----------------+-------+--------+----------------------------+
| id | title    | content        | price | author | create_time                |
+----+----------+----------------+-------+--------+----------------------------+
|  1 | 三国演义 | 孙悟空大闹天宫 |   123 | 吴承恩 | 2020-09-13 14:48:30.000000 |
|  2 | 三国演义 | 东汉末年分三国 |   154 | 罗贯中 | 2020-09-13 12:48:30.962070 |
|  3 | 水浒传   | 刘姥姥进大观园 |   154 | 曹雪芹 | 2020-09-13 12:48:30.962070 |
|  4 | 水浒传   | 逼上梁山       |   140 | 施耐庵 | 2020-09-13 12:48:30.962070 |
|  7 | wed      | dwd            |   123 | 44     | 2020-10-13 17:08:50.493488 |
|  8 | ds       | dwd            |   123 | 32     | 2020-10-13 17:19:18.121264 |
|  9 | 西游记   | 西天取经       |   345 | NULL   | 2020-11-14 15:48:59.000000 |
+----+----------+----------------+-------+--------+----------------------------+
7 rows in set (0.00 sec)
 
-- 查询价格相同的图书种类
mysql> SELECT price, COUNT(*) FROM polls_article GROUP BY price;
+-------+----------+
| price | COUNT(*) |
+-------+----------+
|   123 |        3 |
|   140 |        1 |
|   154 |        2 |
|   345 |        1 |
+-------+----------+
4 rows in set (0.00 sec)

注：
1、上面例子表示：查询表中所有的数据，按照"price"字段值进行分组，然后计算每组的记录条数

2、在创建分组时需要注意，一般来说：
⑴除聚合函数之外，SELECT语句中的每个列都必须在GROUP BY子句中给出

GROUP BY与GROUP_CONCAT

GROUP BY关键字可以和GROUP_CONCAT()函数一起使用。GROUP_CONCAT()函数会把每个分组的字段值都显示出来

例3：


mysql> SELECT title,price, COUNT(*) FROM polls_article GROUP BY price;
+----------+-------+----------+
| title    | price | COUNT(*) |
+----------+-------+----------+
| 三国演义 |   123 |        3 |
| 水浒传   |   140 |        1 |
| 三国演义 |   154 |        2 |
| 西游记   |   345 |        1 |
+----------+-------+----------+
4 rows in set (0.00 sec)
 
mysql> SELECT price,COUNT(*), GROUP_CONCAT(title) AS article_title FROM polls_article GROUP BY price;
+-------+----------+-----------------+
| price | COUNT(*) | article_title   |
+-------+----------+-----------------+
|   123 |        3 | 三国演义,wed,ds |
|   140 |        1 | 水浒传          |
|   154 |        2 | 三国演义,水浒传 |
|   345 |        1 | 西游记          |
+-------+----------+-----------------+
4 rows in set (0.00 sec)

注：可以看出
1、数据分组后，如果直接查询某个列时：只会返回该分组内第一个值(例子中的title列)

2、而使用GROUP_CONCAT()函数，就会返回该分组内所有的值

多字段分组

1、使用GROUP BY可以对多个字段进行分组，GROUP BY关键字后面跟需要分组的字段
2、MYSQL根据多字段的值来进行层次分组，分组层次从左到右
⑴即先按第一个字段分组，然后在第一个字段值相同的记录中，再根据第二个字段的值进行分组...一次类推

例4：


mysql> SELECT * FROM polls_article;
+----+----------+----------------+-------+--------+----------------------------+
| id | title    | content        | price | author | create_time                |
+----+----------+----------------+-------+--------+----------------------------+
|  1 | 三国演义 | 孙悟空大闹天宫 |   123 | 吴承恩 | 2020-09-13 14:48:30.000000 |
|  2 | 三国演义 | 东汉末年分三国 |   154 | 罗贯中 | 2020-09-13 12:48:30.962070 |
|  3 | 水浒传   | 刘姥姥进大观园 |   154 | 曹雪芹 | 2020-09-13 12:48:30.962070 |
|  4 | 水浒传   | 逼上梁山       |   140 | 施耐庵 | 2020-09-13 12:48:30.962070 |
|  7 | ds       | dwd            |   123 | 44     | 2020-10-13 17:08:50.493488 |
|  8 | ds       | dwd            |   123 | 32     | 2020-10-13 17:19:18.121264 |
|  9 | 西游记   | 西天取经       |   345 | NULL   | 2020-11-14 15:48:59.000000 |
+----+----------+----------------+-------+--------+----------------------------+
7 rows in set (0.00 sec)
 
mysql> SELECT * FROM polls_article GROUP BY price,title;
+----+----------+----------------+-------+--------+----------------------------+
| id | title    | content        | price | author | create_time                |
+----+----------+----------------+-------+--------+----------------------------+
|  7 | ds       | dwd            |   123 | 44     | 2020-10-13 17:08:50.493488 |
|  1 | 三国演义 | 孙悟空大闹天宫 |   123 | 吴承恩 | 2020-09-13 14:48:30.000000 |
|  4 | 水浒传   | 逼上梁山       |   140 | 施耐庵 | 2020-09-13 12:48:30.962070 |
|  2 | 三国演义 | 东汉末年分三国 |   154 | 罗贯中 | 2020-09-13 12:48:30.962070 |
|  3 | 水浒传   | 刘姥姥进大观园 |   154 | 曹雪芹 | 2020-09-13 12:48:30.962070 |
|  9 | 西游记   | 西天取经       |   345 | NULL   | 2020-11-14 15:48:59.000000 |
+----+----------+----------------+-------+--------+----------------------------+
6 rows in set (0.00 sec)
 
mysql> SELECT price,COUNT(*), GROUP_CONCAT(title)  FROM polls_article GROUP BY price,title;
+-------+----------+---------------------+
| price | COUNT(*) | GROUP_CONCAT(title) |
+-------+----------+---------------------+
|   123 |        2 | ds,ds               |
|   123 |        1 | 三国演义            |
|   140 |        1 | 水浒传              |
|   154 |        1 | 三国演义            |
|   154 |        1 | 水浒传              |
|   345 |        1 | 西游记              |
+-------+----------+---------------------+
6 rows in set (0.00 sec)

注：上面例子中
1、表示：先按照"price"字段分组，再按"title"字段分组
   ⑴比如：按照"price"字段分组时，有三条数据：ds、ds、西游记
   ⑵此时分组后：组内存在相同值的数据
   ⑶因此这个这三条数据继续按照"title"字段分组

使用HAVING过滤分组

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

2、HAVING关键字是对分组结果进行过滤。WHERE关键字是对表数据进行过滤
⑴两者同时存在时：肯定是先计算WHERE，WHERE排除的记录肯定是不会出现在分组内的

例5：


mysql> SELECT price,COUNT(*), GROUP_CONCAT(title) AS article_title FROM polls_article GROUP BY price;
+-------+----------+-----------------+
| price | COUNT(*) | article_title   |
+-------+----------+-----------------+
|   123 |        3 | 三国演义,ds,ds  |
|   140 |        1 | 水浒传          |
|   154 |        2 | 三国演义,水浒传 |
|   345 |        1 | 西游记          |
+-------+----------+-----------------+
4 rows in set (0.00 sec)
 
-- 数据按照price分组，返回分组后对每组个数进行计数，返回组个数大于等于2的组数据
mysql> SELECT price,COUNT(*), GROUP_CONCAT(title) AS article_title FROM polls_article GROUP BY price HAVING COUNT(title) >=2;
+-------+----------+-----------------+
| price | COUNT(*) | article_title   |
+-------+----------+-----------------+
|   123 |        3 | 三国演义,ds,ds  |
|   154 |        2 | 三国演义,水浒传 |
+-------+----------+-----------------+
2 rows in set (0.00 sec)

例5_1：


mysql> SELECT price,COUNT(*), GROUP_CONCAT(title) AS article_title FROM polls_article WHERE price >130 GROUP BY price;
+-------+----------+-----------------+
| price | COUNT(*) | article_title   |
+-------+----------+-----------------+
|   140 |        1 | 水浒传          |
|   154 |        2 | 三国演义,水浒传 |
|   345 |        1 | 西游记          |
+-------+----------+-----------------+
3 rows in set (0.00 sec)
 
mysql> SELECT price,COUNT(*), GROUP_CONCAT(title) AS article_title FROM polls_article WHERE price >130 GROUP BY price HAVING COUNT(title) >=2;
+-------+----------+-----------------+
| price | COUNT(*) | article_title   |
+-------+----------+-----------------+
|   154 |        2 | 三国演义,水浒传 |
+-------+----------+-----------------+
1 row in set (0.00 sec)

注：
上面SQL表示：查询表中"price >130"的数据(WHERE)->按照"price"分组->返回组数据个数大于等于2的组(HAVING)

GROUP BY与WITH ROLLUP

WITH POLLUP关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量

例6：


mysql> SELECT price,COUNT(*), GROUP_CONCAT(title) AS article_title FROM polls_article GROUP BY price;
+-------+----------+-----------------+
| price | COUNT(*) | article_title   |
+-------+----------+-----------------+
|   123 |        3 | 三国演义,ds,ds  |
|   140 |        1 | 水浒传          |
|   154 |        2 | 三国演义,水浒传 |
|   345 |        1 | 西游记          |
+-------+----------+-----------------+
4 rows in set (0.00 sec)
 
mysql> SELECT price,COUNT(*), GROUP_CONCAT(title) AS article_title FROM polls_article GROUP BY price WITH ROLLUP;
+-------+----------+----------------------------------------------+
| price | COUNT(*) | article_title                                |
+-------+----------+----------------------------------------------+
|   123 |        3 | 三国演义,ds,ds                               |
|   140 |        1 | 水浒传                                       |
|   154 |        2 | 三国演义,水浒传                              |
|   345 |        1 | 西游记                                       |
|  NULL |        7 | 三国演义,ds,ds,水浒传,三国演义,水浒传,西游记 |
+-------+----------+----------------------------------------------+
5 rows in set (0.00 sec)

注：
通过GROUP BY分组之后，在显示结果的最后面新添加了一行，该行每列的值正好是上面所有值之和

GROUP BY与ORDER BY

1、某些情况下需要对分组进行排序
⑴一般情况下ORDER BY是用来对查询结果进行排序的。当其与GROUP BY一起使用时，可以对分组结果进行排序

2、需要注意：当使用ROLLUP时，就不能同时使用ORDER BY子句进行结果排序了
⑴即：ROLLUP和ORDER BY是互相排斥的例7：


mysql> SELECT price,COUNT(*), GROUP_CONCAT(title) AS article_title FROM polls_article GROUP BY price;
+-------+----------+-----------------+
| price | COUNT(*) | article_title   |
+-------+----------+-----------------+
|   123 |        3 | 三国演义,ds,ds  |
|   140 |        1 | 水浒传          |
|   154 |        2 | 三国演义,水浒传 |
|   345 |        1 | 西游记          |
+-------+----------+-----------------+
4 rows in set (0.00 sec)
 
mysql> SELECT price,COUNT(*), GROUP_CONCAT(title) AS article_title FROM polls_article GROUP BY price ORDER BY article_title;
+-------+----------+-----------------+
| price | COUNT(*) | article_title   |
+-------+----------+-----------------+
|   123 |        3 | 三国演义,ds,ds  |
|   154 |        2 | 三国演义,水浒传 |
|   140 |        1 | 水浒传          |
|   345 |        1 | 西游记          |
+-------+----------+-----------------+
4 rows in set (0.00 sec)

例7_1：


mysql> SELECT price,COUNT(*), GROUP_CONCAT(title) AS article_title FROM polls_article GROUP BY price HAVING COUNT(article_title) >= 1;
+-------+----------+-----------------+
| price | COUNT(*) | article_title   |
+-------+----------+-----------------+
|   123 |        3 | 三国演义,ds,ds  |
|   140 |        1 | 水浒传          |
|   154 |        2 | 三国演义,水浒传 |
|   345 |        1 | 西游记          |
+-------+----------+-----------------+
4 rows in set (0.00 sec)
 
mysql>  SELECT price,COUNT(*), GROUP_CONCAT(title) AS article_title FROM polls_article GROUP BY price HAVING COUNT(article_title) >= 1 ORDER BY article_title;
+-------+----------+-----------------+
| price | COUNT(*) | article_title   |
+-------+----------+-----------------+
|   123 |        3 | 三国演义,ds,ds  |
|   154 |        2 | 三国演义,水浒传 |
|   140 |        1 | 水浒传          |
|   345 |        1 | 西游记          |
+-------+----------+-----------------+
4 rows in set (0.00 sec)

MySQL和Oracle使用GROUP BY的区别

1、Oracle在使用group by时，查询字段必须是分组的依据或聚合函数
⑴select子句后的任一非聚合函数字段都应来源于group by分组语句后，否则语法会编译不通过
⑵也就是说SELECT子句后的所有查询列(除聚合函数外)，都应该与GROUP BY子句后的列一致

2、MySQL没有此限制，会自动取第一行
⑴SELECT子句后的所有查询列(除聚合函数外)，可以与GROUP BY子句后的列不一致
⑵注：虽然Mysql中无限制，但是在写SQL时最好还是保证SELECT子句后的所有查询列(除聚合函数外)，都与GROUP BY子句后的列一致

例8：Mysql


mysql> SELECT * FROM FRUITS; --查询所有数据
+------+--------+---------+
| f_id | f_name | f_price |
+------+--------+---------+
|    1 | apple  | 134     |
|    2 | origen | 333     |
|    3 | caomei | 134     |
|    4 | li     | 334     |
+------+--------+---------+
4 rows in set (0.00 sec)
 
mysql> SELECT * FROM FRUITS GROUP BY f_price; --查询所有列：查询列与GROUP BY子句列名不一致
--f_price=134的数据有两条，mysql在这中分组情况下就只会返回第一条数据，mysql只是特殊处理过
--oracle就没有特殊处理，这种情况下f_price=134的有两条数据，但是查询的又是f_name，且这两条数据的值不一致，就无法分组，此时数据库就没法知道该返回哪条数据
--所以在oracle中查询列必须与分组依据一致：比如这种情况下载oracle中就必须再按照f_name进行分组
+------+--------+---------+
| f_id | f_name | f_price |
+------+--------+---------+
|    1 | apple  | 134     |
|    2 | origen | 333     |
|    4 | li     | 334     |
+------+--------+---------+
3 rows in set (0.00 sec)
 
mysql> SELECT f_name FROM FRUITS GROUP BY f_price;  --查询指定列：查询列与GROUP BY子句列名不一致
+--------+
| f_name |
+--------+
| apple  |
| origen |
| li     |
+--------+
3 rows in set (0.00 sec)
 
mysql> SELECT f_name,COUNT(f_price) FROM FRUITS GROUP BY f_price;
+--------+----------------+
| f_name | COUNT(f_price) |
+--------+----------------+
| apple  |              2 |
| origen |              1 |
| li     |              1 |
+--------+----------------+
3 rows in set (0.00 sec)
 
mysql> SELECT f_name,f_price FROM FRUITS GROUP BY f_price,f_name;
+--------+---------+
| f_name | f_price |
+--------+---------+
| apple  | 134     |
| caomei | 134     |
| origen | 333     |
| li     | 334     |
+--------+---------+
4 rows in set (0.00 sec)

例8_1：Oracle


SQL> SELECT * FROM FRUITS;  --查询所有数据
 
      F_ID F_NAME               F_PRICES
---------- -------------------- --------------------
         3 apple                345
         4 origin               123
         5 APPLE                343
         6 CAOMEI               123
 
SQL> SELECT * FROM FRUITS GROUP BY f_prices;  --查询所有列：查询列与GROUP BY子句列名不一致
SELECT * FROM FRUITS GROUP BY f_prices
       *
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
 
 
SQL> SELECT f_name FROM FRUITS GROUP BY f_prices;  --查询指定列：查询列与GROUP BY子句列名不一致
SELECT f_name FROM FRUITS GROUP BY f_prices
       *
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
 
 
SQL> SELECT f_name,count(*) FROM FRUITS GROUP BY f_prices;  --查询指定列：查询列与GROUP BY子句列名不一致
SELECT f_name,count(*) FROM FRUITS GROUP BY f_prices
       *
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
 
 
SQL> SELECT f_name,f_prices,count(*) FROM FRUITS GROUP BY f_prices,f_name; --查询指定列：查询列与GROUP BY子句列名一致
 
F_NAME               F_PRICES               COUNT(*)
-------------------- -------------------- ----------
apple                345                           1
origin               123                           1
APPLE                343                           1
CAOMEI               123                           1

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/羊村懒王/article/detail/407012