mysql中什么用来分组_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)列的值是上面分组值的总和。*/