查询分组与行计数
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从句并没有什么意义,该从句的真正作用在于与各种组合函数配合,用于行计数。
1、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 |
+---------+------+----------+
--转自