mysql> select owner, count(*) from pet;
+--------+----------+
| owner | count(*) |
+--------+----------+
| Harold | 8 |
+--------+----------+
手册上不是说会出错吗?为什么返回这样的答案?我的表内容:mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 0000-00-00 | NULL |
| Whistler | Gwen | bird | | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+--------+---------+------+------------+------------+
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): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR
OUP columns is illegal if there is no GROUP BY clausemysql> set @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)mysql> select owner, count(*) from pet;
+--------+----------+
| owner | count(*) |
+--------+----------+
| Harlod | 2 |
+--------+----------+
1 row in set (0.00 sec)总之,mysql在这方面非常灵活,但同时也让人很费解。
select owner, count(*) from pet group by owner;
MySQL 扩展了 GROUP BY的用途,因此你可以使用SELECT 列表中不出现在GROUP BY语句中的列或运算。这代表 “对该组的任何可能值 ”。你可以通过避免排序和对不必要项分组的办法得到它更好的性能。例如,在下列问询中,你无须对customer.name 进行分组: mysql> SELECT order.custid, customer.name, MAX(payments) -> FROM order,customer -> WHERE order.custid = customer.custid -> GROUP BY order.custid;在标准SQL中, 你必须将 customer.name添加到 GROUP BY子句中。在MySQL中, 假如你不在ANSI模式中运行,则这个名字就是多余的。 假如你从 GROUP BY 部分省略的列在该组中不是唯一的,那么不要使用这个功能! 你会得到非预测性结果。