MySQL GROUP BY の骚操作

发表于 更新于 分类于 Disqus：

源自https://mariadb.com/kb/en/library/group-by/#group-by-examples

Use the GROUP BY clause in a SELECT statement to group rows together that have the same value in one or more column, or the same computed value using expressions with any functions and operators except grouping functions. When you use a GROUP BY clause, you will get a single result row for each group of rows that have the same value for the expression given in GROUP BY.

于是

  1. group by 一个字段
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select count(*), channel from wlan_client group by channel;
+----------+---------+
| count(*) | channel |
+----------+---------+
|       24 |       0 |
|    39767 |       1 |
|    37896 |       6 |
|    48757 |      11 |
|    17398 |      36 |
|     9076 |      40 |
|    13223 |      44 |
|    10594 |      48 |
|    13582 |      52 |
|     5804 |      56 |
|    13966 |      60 |
|     6515 |      64 |
|    19984 |     149 |
|    14560 |     153 |
|    19113 |     157 |
|    21748 |     161 |
|       31 |     165 |
+----------+---------+
17 rows in set (0.41 sec)

这是最常见的操作，没毛病，channel的每一个值都会get a single result row

  1. group by channel > 15
1
2
3
4
5
6
7
8
mysql> select count(*), channel from wlan_client group by channel > 15;
+----------+---------+
| count(*) | channel |
+----------+---------+
|   126444 |      11 |
|   165594 |      44 |
+----------+---------+
2 rows in set (0.41 sec)

说实话我并没有找到有明确的文档说明此用法，但从上面引用的文档原文来看，也能说通，所有channel > 15的被归为了一类，其它的归为更一类，所以结果有两行

可以用简单的count验证

1
2
3
4
5
6
7
mysql> select count(*), channel from wlan_client where channel > 15;
+----------+---------+
| count(*) | channel |
+----------+---------+
|   165594 |      44 |
+----------+---------+
1 row in set (0.30 sec)
  1. group by 整数

You can also use a single integer as the grouping expression. If you use an integer n, the results will be grouped by the nth column in the select expression.

这里是另一种用法，N代表select中的第N项，如上面的select count(), channel from wlan_client group by channel就可写成select count(), channel from wlan_client group by 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select count(*), channel from wlan_client group by 2;
+----------+---------+
| count(*) | channel |
+----------+---------+
|       24 |       0 |
|    39767 |       1 |
|    37896 |       6 |
|    48757 |      11 |
|    17398 |      36 |
|     9076 |      40 |
|    13223 |      44 |
|    10594 |      48 |
|    13582 |      52 |
|     5804 |      56 |
|    13966 |      60 |
|     6515 |      64 |
|    19984 |     149 |
|    14560 |     153 |
|    19113 |     157 |
|    21748 |     161 |
|       31 |     165 |
+----------+---------+
17 rows in set (0.40 sec)
  1. group by 1+1 或者 2 > 15

恩，此时mysql不会认为1+1=2，然后同[3], 也不会认为2是channel然后同[2]，而是只返回了一条

1
2
3
4
5
6
7
mysql> select count(*), channel from wlan_client group by 2 > 15;
+----------+---------+
| count(*) | channel |
+----------+---------+
|   292038 |      44 |
+----------+---------+
1 row in set (0.30 sec)

这里的count(*)的确是该表的总行数，那么这又是什么意思呢

猜测，这里并没有可以分组的列维度，所以结果应该跟直接count(*)没有group by一样，但既然有group by，又没有找到可以用来计算分组维度的列，mysql竟然没有报错，很是神奇

明明这样是会报错的

1
2
mysql> select count(*), channel from wlan_client group by 0;
ERROR 1054 (42S22): Unknown column '0' in 'group statement'