1

计数行

数据库通常用于回答“表中某种类型的数据出现频率多少?”的问题。例如,你可能想知道你拥有多少只宠物,或每个拥有者拥有多少只宠物,或者你可能想要对你的动物进行各种类型的普查操作。

计算你拥有的动物总数与“pet表中有多少行?”的问题相同。因为每只宠物有一条记录,COUNT(*)计算行数,因此计算动物的查询如下所示:

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

之前,你检索了拥有宠物的人的姓名,如果你想知道每个拥有者有多少宠物,你可以使用COUNT()

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

上述查询使用GROUP BY对每个owner的所有记录进行分组,将COUNT()GROUP BY结合使用在各种分组下描述你的数据非常有用,以下示例显示了执行动物普查操作的不同方法。

每种动物数量:

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

每种性别的动物数量:

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

在此输出中,NULL表示性别未知。

每种物种和性别组合的动物数量:

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 |
+---------+------+----------+

使用COUNT()时无需检索整个表,例如,之前的查询,只在狗和猫上执行时,如下所示:

mysql> SELECT species, sex, COUNT(*) FROM pet
       WHERE species = 'dog' OR species = 'cat'
       GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

或者,如果你想要每种性别的动物数量仅适用于已知性别的动物:

mysql> SELECT species, sex, COUNT(*) FROM pet
       WHERE sex IS NOT NULL
       GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

如果在COUNT()值之外指定要选择的列的名称,应该存在一个GROUP BY子句来命名那些相同的列,否则,会发生以下情况:

  • 如果启用了ONLY_FULL_GROUP_BY SQL模式,则会发生错误:

    mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT owner, COUNT(*) FROM pet;
    ERROR 1140 (42000): In aggregated query without GROUP BY, expression
    #1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
    this is incompatible with sql_mode=only_full_group_by
  • 如果未启用ONLY_FULL_GROUP_BY,则通过将所有行视为单个组来处理查询,但为每个命名列选择的值是不确定的,服务器可以自由选择任何行中的值:

    mysql> SET sql_mode = '';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT owner, COUNT(*) FROM pet;
    +--------+----------+
    | owner  | COUNT(*) |
    +--------+----------+
    | Harold |        8 |
    +--------+----------+

上一篇:模式匹配

博弈
2.5k 声望1.5k 粉丝

态度决定一切