MySQL函数&关键字（二）分组 group by/group_concat()

MySQL 官方手册 8.0 Reference Manual - GROUP BY Modifiers
MySQL 官方手册 8.0 Reference Manual - GROUP_CONCAT(expr)

1、group by

​ 通过select在返回集字段中，这些字段要么就要包含在group by语句后面 作为分组的依据，要么就要被包含在聚合函数中。否则就可能存在多个值，如上图，张三的语文有74，0，0三个值。

with rollup： group by 后可以跟with rollup，表示在进行分组统计的基础上再次进行汇总统计（在每个分组下都会有统计汇总）

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子句进行结果集的过滤。

基本语法：

SELECT   select_expr [, select_expr ...]

   FROM  table_name

   [WHERE where_condition]

   [GROUP BY {col_name | expr} [ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

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子句中(否则出错)

mysql> select town,count(*)
    -> from PLAYERS
    -> group by town
    -> having birth_date>'1970-01-01';
ERROR 1054 (42S22): Unknown column 'birth_date' in 'having clause'
mysql> select town,count(*)
    -> from PLAYERS
    -> group by town
    -> having town in ('Eltham','Midhurst');
+----------+----------+
| town     | count(*) |
+----------+----------+
| Eltham   |        2 |
| Midhurst |        1 |
+----------+----------+
2 rows in set (0.00 sec)

2、GROUP_CONCAT()

将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

例1：对于每个球队，得到其编号和所有球员的编号

mysql> select name,group_concat(id order by id desc separator ',') 'group_concat'
    -> from t1
    -> group by name;
+--------+------------------------+
| name   | group_concat           |
+--------+------------------------+
|老王     | 83,57,44,8,6,2         |
|张三     | 112,104,27,8           |
+--------+------------------------+
2 rows in set (0.01 sec)

如果没有group by子句，group_concat返回一列的所有值

例2：得到所有的罚款编号列表

mysql> select group_concat(paymentno)
    -> from PENALTIES;
+-------------------------+
| group_concat(paymentno) |
+-------------------------+
| 1,2,3,4,5,6,7,8         |
+-------------------------+
1 row in set (0.00 sec)