数据库

MySQL-分组group by 

1
2
3
mysql> select name,age from user group by age;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.user.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>

使用group by进行分组时，不应该查询别的字段，例如这里的name,因为MySQL不知道要显示哪个用户，我们只需要查询分组的字段即可，或者进行一些统计计算，如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
mysql> select * from user;
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  1 | zhangsan |  16 | W   |
|  2 | lisi     |  21 | M   |
|  3 | laoliu   |  24 | W   |
|  4 | jianzhe  |  16 | W   |
|  5 | wangwei  |  21 | M   |
+----+----------+-----+-----+
5 rows in set (0.00 sec)

mysql> select age from user group by age;
+-----+
| age |
+-----+
|  16 |
|  21 |
|  24 |
+-----+
3 rows in set (0.00 sec)

mysql> select age,count(age) from user group by age;
+-----+------------+
| age | count(age) |
+-----+------------+
|  16 |          2 |
|  21 |          2 |
|  24 |          1 |
+-----+------------+
3 rows in set (0.00 sec)

mysql> select age,sum(age) from user group by age;
+-----+----------+
| age | sum(age) |
+-----+----------+
|  16 |       32 |
|  21 |       42 |
|  24 |       24 |
+-----+----------+
3 rows in set (0.02 sec)

mysql> select age,sum(age) from user group by age having age > 20;
+-----+----------+
| age | sum(age) |
+-----+----------+
|  21 |       42 |
|  24 |       24 |
+-----+----------+
2 rows in set (0.00 sec)

mysql> select age,sum(age) from user where age > 20 group by age;#推荐使用where,age有索引的话where可以使用索引
+-----+----------+
| age | sum(age) |
+-----+----------+
|  21 |       42 |
|  24 |       24 |
+-----+----------+
2 rows in set (0.00 sec)

mysql> select age,sex,count(*) from user group by age,sex;
+-----+-----+----------+
| age | sex | count(*) |
+-----+-----+----------+
|  16 | W   |        2 |
|  21 | M   |        2 |
|  24 | W   |        1 |
+-----+-----+----------+
3 rows in set (0.00 sec)

mysql>

mysql> select age,sex,count(id) from user group by age,sex order by age desc; 
+-----+-----+-----------+
| age | sex | count(id) |
+-----+-----+-----------+
|  24 | W   |         1 |
|  21 | M   |         2 |
|  16 | W   |         2 |
+-----+-----+-----------+
3 rows in set (0.01 sec)

mysql>

其实，从上面可以看出，其实order by内部实际上是进行了排序的，我们使用explain查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> explain select age from user group by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name from user group by name;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | name          | name | 202     | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

可以看到，在使用没有索引的字段排序时，MySQL使用的是Using temporary，使用临时表进行排序，而使用有索引的name排序时，是使用索引树进行排序的，显然，group by在分组时的效率与索引有关系