当前位置: Oracle DBA培训网-优技培训 >> MySQL培训 > MySQL培训教程 >

MySQL培训课程:Group By查询分组与Count计数

MySQL培训课程:Group By查询分组与Count计数

MySQL培训课程:Group By查询分组与Count计数,GROUP BY 从句根据所给的列名返回分组的查询结果,可用于查询具有相同值的列。其语法为:

GROUP BY col_name,….

可以为多个列分组

例如:

mysql>select * from pet group by species;

+----------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+----------+--------+---------+------+------------+-------+

| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Puffball | Diane | hamster | f | 1999-03-30 | NULL |

| Slim | Benny | snake | m | 1996-04-29 | NULL |

+----------+--------+---------+------+------------+-------+

由以上结果可以看出:

查询显示结果时,被分组的列如果有重复的值,只返回靠前的记录,并且返回的记录集是排序的。这并不是一个很好的结果。仅仅使用GROUP BY从句并没有什么意义,该从句的真正作用在于与各种组合函数配合,用于行计数。

COUNT()函数计数非NULL结果的数目。

你可以这样计算表中记录行的数目:

mysql> select count(*) from pet;

+----------+

| count(*) |

+----------+

| 9 |

+----------+

计算sex为非空的记录数目:

mysql> select count(sex) from pet;

+------------+

| count(sex) |

+------------+

| 8        |

+------------+

现在配合GROUP BY 从句使用。

例如:要知道每个主人有多少宠物

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

+--------+----------+

| owner | COUNT(*) |

+--------+----------+

| Benny | 2 |

| Diane | 2 |

| Gwen | 3 |

| Harold | 2 |

+--------+----------+

又如,每种宠物的个数:

mysql> SELECT species,count(*) FROM pet GROUP BY species;

+---------+----------+

| species | count(*) |

+---------+----------+

| bird | 2 |

| cat | 2 |

| dog | 3 |

| hamster | 1 |

| snake | 1 |

+---------+----------+

如果你除了计数还返回一个列的值,那么必须使用GROU BY语句,否则无法计算记录。例如上例,使用GROUP BY对每个owner分组所有记录,没有它,你得到的一切是一条错误消息:

mysql> SELECT owner, COUNT(owner) FROM pet;

ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)

with no GROUP columns is illegal if there is no GROUP BY clause

也可以根据多个列分组

例如:按种类和性别组合的动物数量:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

+---------+------+----------+

| species | sex | COUNT(*) |

+---------+------+----------+

| bird | NULL | 1 |

| bird | f | 1 |

| cat | f | 1 |

| cat | m | 1 |

| dog | f | 1 |

| dog | m | 2 |

| hamster | f | 1 |

| snake | m | 1 |

+---------+------+----------+

技术沙龙MORE+

标签错误:<!-- #Label# labelId=20160707140604 moduleId=1 classId=12231768634 orderby=2 fields=url,title,u_info attribute= datatypeId=22192428132 recordCount=3 pageSize= <htmlTemplate><dt><img src="/images/index_26${index}.jpg" width="100" height="62" /><a href="$url" title="${title}">${title}</a><span>${api.left(u_info,60)}</span></dt></htmlTemplate> -->
我要参加技术沙龙