mysql - 同样的条件，不同的结果？

Question

通过阅读 MySQL 文档，我无法解释 phpMyAdmin 中这两个查询之间的区别:

SELECT * FROM f_ean GROUP BY ean HAVING type = 'media'

--> 给我 57059 个结果

SELECT ean, type FROM f_ean GROUP BY ean HAVING type = 'media'

--> 给我 73201 个结果

仅仅显示不同的列，查询的结果数怎么可能不同？

Answer 1

如果您尝试过滤记录，您应该使用WHERE，而不是HAVING。 HAVING 用于在分组和排序发生后应用过滤器。

无论如何，问题在于 MySQL 如何使用 GROUP BY。 GROUP BY 应该与聚合一起使用； MySQL 为方便起见扩展了功能。由于对列进行排序和分组的方式，您会收到不同的结果。

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group.

参见 extensions to GROUP BY .