MySQL基礎(chǔ)教程：Group By查詢分組與Count行計(jì)數(shù)，GROUP BY 從句根據(jù)所給的列名返回分組的查詢結(jié)果，可用于查詢具有相同值的列。其語(yǔ)法為：

GROUP BY col_name，….

可以為多個(gè)列分組

例如：

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 |

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

由以上結(jié)果可以看出：

查詢顯示結(jié)果時(shí)，被分組的列如果有重復(fù)的值，只返回靠前的記錄，并且返回的記錄集是排序的。這并不是一個(gè)很好的結(jié)果。僅僅使用GROUP BY從句并沒(méi)有什么意義，該從句的真正作用在于與各種組合函數(shù)配合，用于行計(jì)數(shù)。

COUNT()函數(shù)計(jì)數(shù)非NULL結(jié)果的數(shù)目。

你可以這樣計(jì)算表中記錄行的數(shù)目：

mysql> select count(*) from pet;

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

| count(*) |

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

| 9 |

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

計(jì)算sex為非空的記錄數(shù)目：

mysql> select count(sex) from pet;

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

| count(sex) |

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

| 8        |

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

現(xiàn)在配合GROUP BY 從句使用。

例如：要知道每個(gè)主人有多少寵物

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

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

| owner | COUNT(*) |

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

| Benny | 2 |

| Diane | 2 |

| Gwen | 3 |

| Harold | 2 |

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

又如，每種寵物的個(gè)數(shù)：

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

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

| species | count(*) |

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

| bird | 2 |

| cat | 2 |

| dog | 3 |

| hamster | 1 |

| snake | 1 |

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

如果你除了計(jì)數(shù)還返回一個(gè)列的值，那么必須使用GROU BY語(yǔ)句，否則無(wú)法計(jì)算記錄。例如上例，使用GROUP BY對(duì)每個(gè)owner分組所有記錄，沒(méi)有它，你得到的一切是一條錯(cuò)誤消息：

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

也可以根據(jù)多個(gè)列分組

例如：按種類和性別組合的動(dòng)物數(shù)量：

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 |

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