MySQL中group by分组查询介绍

group by 属性名 [having 条件表达式][with rollup] 属性名：指按照该字段的值进行分组。 having条件表达式：限制分组后的现实，满足条件表达式的结果将被显示。 with rollup：会在所有记录的最后加上一条记录。该记录是上面所有记录的总和。 group by可以和group_concat()函数一起使用。group_concat()函数会将每个分组中指定字段值都显示出来。 group by通常与集合函数(count()、sum()、avg()、max()、min())一起使用。

mysql> /*执行不带group by的select语句*/
mysql> select * from employee;
+-----+------+------+-----+-----+--------------+
| num | d_id | name | age | sex | homeaddr     |
+-----+------+------+-----+-----+--------------+
|   1 | 1001 | 张三 |  26 | 男  | 北京市海淀区 |
|   2 | 1001 | 李四 |  24 | 女  | 北京市昌平区 |
|   3 | 1002 | 王五 |  25 | 男  | 湖南长沙市   |
|   4 | 1004 | Aric |  15 | 男  | England      |
+-----+------+------+-----+-----+--------------+
4 rows in set (0.00 sec)

mysql> /*单独使用group by分组*/
mysql> select * from employee group by sex;
+-----+------+------+-----+-----+--------------+
| num | d_id | name | age | sex | homeaddr     |
+-----+------+------+-----+-----+--------------+
|   2 | 1001 | 李四 |  24 | 女  | 北京市昌平区 |
|   1 | 1001 | 张三 |  26 | 男  | 北京市海淀区 |
+-----+------+------+-----+-----+--------------+
2 rows in set (0.00 sec)

mysql> /*group by与group_concat()函数一起使用*/
mysql> select sex,group_concat(name) from employee group by sex;
+-----+--------------------+
| sex | group_concat(name) |
+-----+--------------------+
| 女  | 李四               |
| 男  | 张三,王五,Aric     |
+-----+--------------------+
2 rows in set (0.01 sec)

mysql> /*group by与集合函数一起使用*/
mysql> select sex,count(num),avg(age) from employee group by sex;
+-----+------------+----------+
| sex | count(num) | avg(age) |
+-----+------------+----------+
| 女  |          1 |  24.0000 |
| 男  |          3 |  22.0000 |
+-----+------------+----------+
2 rows in set (0.00 sec)

mysql> /*group by与having一起使用*/
mysql> select sex,count(sex) from employee group by sex having count(num)>=3;
+-----+------------+
| sex | count(num) |
+-----+------------+
| 男  |          3 |
+-----+------------+
1 row in set (0.00 sec)

mysql> select sex,avg(age),count(num) from employee group by sex having count(num)>=3 or avg(age)>23;
+-----+----------+------------+
| sex | avg(age) | count(num) |
+-----+----------+------------+
| 女  |  24.0000 |          1 |
| 男  |  22.0000 |          3 |
+-----+----------+------------+
2 rows in set (0.00 sec)
/*having条件表达式与where条件表达式一样，都是用来限制显示的。having作用于分组后的记录，用于选择满足条件的组；where作用于表或视图，是表和视图的查询条件。*/

mysql> /*按多个字段分组*/
mysql> select * from employee group by d_id,sex;
+-----+------+------+-----+-----+--------------+
| num | d_id | name | age | sex | homeaddr     |
+-----+------+------+-----+-----+--------------+
|   2 | 1001 | 李四 |  24 | 女  | 北京市昌平区 |
|   1 | 1001 | 张三 |  26 | 男  | 北京市海淀区 |
|   3 | 1002 | 王五 |  25 | 男  | 湖南长沙市   |
|   4 | 1004 | Aric |  15 | 男  | England      |
+-----+------+------+-----+-----+--------------+
4 rows in set (0.00 sec)

mysql> insert into employee values(null,1001,'aiml',26,'男','北京市海淀区'
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee group by d_id,sex;
+-----+------+------+-----+-----+--------------+
| num | d_id | name | age | sex | homeaddr     |
+-----+------+------+-----+-----+--------------+
|   2 | 1001 | 李四 |  24 | 女  | 北京市昌平区 |
|   1 | 1001 | 张三 |  26 | 男  | 北京市海淀区 |
|   3 | 1002 | 王五 |  25 | 男  | 湖南长沙市   |
|   4 | 1004 | Aric |  15 | 男  | England      |
+-----+------+------+-----+-----+--------------+
4 rows in set (0.00 sec)

mysql> /*group by与with rollup一起使用*/
mysql> select sex,count(num) from employee group by sex with rollup;
+-----+------------+
| sex | count(num) |
+-----+------------+
| 女  |          1 |
| 男  |          4 |
| NULL |          5 |
+-----+------------+
3 rows in set (0.00 sec)
/*在记录最后加上了一条新的纪录，该记录count(num)列的值是上面分组值的总和。*/