mysql修改groupby全局设置

转载

mob64ca14048514 2024-09-14 08:48:11

文章标签 mysql修改groupby全局设置 mysql 数据库运维函数返回 文章分类 MySQL 数据库

语法：

SELECT   select_expr [, select_expr ...] 
   FROM  table_name 
   [WHERE where_condition] 
   [GROUP BY {col_name | expr} [ASC | DESC], ... [WITH ROLLUP]]

GROUP BY 子句根据给定列或者表达式的每一个不同的值将表中的行分成不同的组。使用组函数返回每一组的统计信息。

指定一个列进行分组

查询每个城市的名称和球员的数量

root@TENNIS 15:52  mysql>SELECT  town, count(*)   FROM  PLAYERS   GROUP BY  town;
+-----------+----------+
| town      | count(*) |
+-----------+----------+
| Douglas   |        1 |
| Inglewood |        2 |
| Stratford |        6 |
+-----------+----------+
3 rows in set (0.00 sec)

使用多个分组列，形成“大组中再分小组”的分组效果

统计每个球队中每个球员所赢得的总局数

root@TENNIS 15:53  mysql>SELECT teamno, playerno, sum(won)   FROM  MATCHES   GROUP BY  teamno, playerno;
+--------+----------+----------+
| teamno | playerno | sum(won) |
+--------+----------+----------+
|      1 |        2 |        1 |
|      1 |        6 |        8 |
|      1 |        8 |        0 |
|      1 |       44 |        3 |
|      1 |       57 |        3 |
|      1 |       83 |        0 |
|      2 |        8 |        0 |
|      2 |       27 |        3 |
|      2 |      104 |        3 |
|      2 |      112 |        3 |
+--------+----------+----------+
10 rows in set (0.01 sec)

根据表达式分组

对于PENALTIES表中的每一年，得到支付罚款的次数

root@TENNIS 15:54  mysql>SELECT  year(payment_date), count(*)   FROM  PENALTIES   GROUP BY  year(payment_date);
+--------------------+----------+
| year(payment_date) | count(*) |
+--------------------+----------+
|               1980 |        4 |
|               1981 |        2 |
|               1982 |        1 |
|               1983 |        2 |
|               1984 |        3 |
+--------------------+----------+
5 rows in set (0.00 sec)

带有排序的分组

如果分组列和排序列相同，则可以合并group by和order by子句
得到每个球队的编号和比赛总场数，结果按球队编号降序排序？

root@TENNIS 15:55  mysql>SELECT  teamno, count(*)   FROM  MATCHES   GROUP BY  teamno   ORDER BY  teamno DESC;
+--------+----------+
| teamno | count(*) |
+--------+----------+
|      2 |        5 |
|      1 |        8 |
+--------+----------+
2 rows in set (0.00 sec)

可以把desc(或者asc)包含到group by子句中简化

root@TENNIS 15:56  mysql>SELECT  teamno, count(*)   FROM  MATCHES   GROUP BY  teamno DESC;
+--------+----------+
| teamno | count(*) |
+--------+----------+
|      2 |        5 |
|      1 |        8 |
+--------+----------+
2 rows in set, 1 warning (0.00 sec)

GROUP BY子句的规则

1、出现在SELECT子句中的单独的列，必须出现在GROUP BY子句中作为分组列。这条规则适用于其它数据库，但是不适用于MYSQL。例如：

root@TENNIS 15:57  mysql>SELECT  town, count(*)   FROM  PLAYERS   GROUP BY  sex;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'TENNIS.PLAYERS.TOWN' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在MYSQL中执行，不抛出异常，而是返回结果。其中town列的值随机返回。通过设置sql_mode系统变量的值为ONLY_FULL_GROUP_BY来强制mysql和其它数据库一样，遵循该规则（推荐）

2、分组列可以不出现在SELECT子句中

3、分组列可出现在SELECT子句中的一个复合表达式中

得到以美分为单位的罚款额的列表？

root@TENNIS 15:59  mysql>SELECT  cast(amount * 100 AS SIGNED INTEGER) AS amount_in_cents   FROM  PENALTIES   GROUP BY  amount;
+-----------------+
| amount_in_cents |
+-----------------+
|            2500 |
|            3000 |
|            5000 |
|            7500 |
|           10000 |
+-----------------+
5 rows in set (0.00 sec)

4、如果GROUP BY后面是一个复合表达式，那么在SELECT子句中，它必须整体作为一个表达式的一部分才能使用。

GROUP_CONCAT()函数

MYSQL特有的组函数。该函数的值等于属于一个组的指定列的所有值。这些值一个挨一个的放置，以逗号隔开，并且以字符串表示。

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

root@TENNIS 15:59  mysql>SELECT  teamno, group_concat(playerno)
    ->   FROM  MATCHES
    ->   GROUP BY  teamno;
+--------+------------------------+
| teamno | group_concat(playerno) |
+--------+------------------------+
|      1 | 6,6,6,44,83,2,57,8     |
|      2 | 27,104,112,112,8       |
+--------+------------------------+
2 rows in set (0.00 sec)

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

得到所有的罚款编号列表?

root@TENNIS 16:01  mysql>SELECT  group_concat(paymentno)
    ->   FROM  PENALTIES;
+---------------------------------+
| group_concat(paymentno)         |
+---------------------------------+
| 2,3,4,5,6,7,8,9,102,103,104,109 |
+---------------------------------+
1 row in set (0.00 sec)

系统变量group_concat_max_len控制该函数返回的最大字符长度，默认1024。可通过set语句修改：

Set @@group_concat_max_len=7;

使用WITH ROLLUP子句

用来要求在一条GROUP BY子句中进行多个不同的分组
得到每个球员的编号，罚款总和以及所有球员的罚款总和?

root@TENNIS 16:01  mysql>SELECT  playerno, sum(amount)
    ->   FROM  PENALTIES
    ->   GROUP BY  playerno
    -> UNION ALL
    -> SELECT  NULL, sum(amount)
    ->   FROM  PENALTIES;
+----------+-------------+
| playerno | sum(amount) |
+----------+-------------+
|        6 |      200.00 |
|        8 |       25.00 |
|       27 |      350.00 |
|       44 |      205.00 |
|      104 |       50.00 |
|     NULL |      830.00 |
+----------+-------------+
6 rows in set (0.01 sec)

第一个查询按照每个球员分组，第二个查询按照所有球员分组
改写上例:

root@TENNIS 16:03  mysql>SELECT  playerno, sum(amount)
    ->   FROM  PENALTIES
    ->   GROUP BY  playerno  WITH ROLLUP;
+----------+-------------+
| playerno | sum(amount) |
+----------+-------------+
|        6 |      200.00 |
|        8 |       25.00 |
|       27 |      350.00 |
|       44 |      205.00 |
|      104 |       50.00 |
|     NULL |      830.00 |
+----------+-------------+
6 rows in set (0.00 sec)

with rollup表明在按playerno分组之后，还需要另一个分组。

一般来说，如果有子句GROUP BY E1,E2,E3,E4 WITH ROLLUP，那么将分别执行以下分组：[E1,E2,E3,E4]、[E1,E2,E3]、[E1,E2]、[E1]、[]。[]表示所有行都分在一组中。

按照球员的性别和居住城市，统计球员的总数；统计每个性别球员的总数；统计所有球员的总数？

root@TENNIS 16:03  mysql>SELECT  sex, town, count(*)
    ->   FROM  PLAYERS
    ->   GROUP BY  sex,town WITH ROLLUP;
+-----+-----------+----------+
| sex | town      | count(*) |
+-----+-----------+----------+
| F   | Inglewood |        1 |
| F   | NULL      |        1 |
| M   | Douglas   |        1 |
| M   | Inglewood |        1 |
| M   | Stratford |        6 |
| M   | NULL      |        8 |
| NULL | NULL      |        9 |
+-----+-----------+----------+
7 rows in set (0.00 sec)

对分组结果进行过滤

不能使用WHERE子句对分组后的结果进行过滤
不能在WHERE子句中使用组函数
例：得到那些多于一次罚款的球员的编号

root@TENNIS 16:05  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可以单独使用而不和GROUP BY配合
HAVING子句中可以使用组函数

例如：得到那些多于一次罚款的球员的编号

root@TENNIS 16:05  mysql>SELECT  playerno
    ->   FROM  PENALTIES
    ->   GROUP BY  playerno
    ->   HAVING  count(*) > 1;
+----------+
| playerno |
+----------+
|        6 |
|       27 |
|       44 |
+----------+
3 rows in set (0.00 sec)

如果只有HAVING子句而没有GROUP BY，表中所有的行分为一组
例：得到所有罚款的总和，要求总和大于250元时才显示

root@TENNIS 16:06  mysql>SELECT sum(amount)
    ->   FROM PENALTIES
    ->   HAVING sum(amount) > 250;
+-------------+
| sum(amount) |
+-------------+
|      830.00 |
+-------------+
1 row in set (0.00 sec)

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

root@TENNIS 16:07  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'

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。