在同一select子句中使用count(*)和*会产生错误.为什么? [英] Using count(*) and * in the same select clause gives an error. Why?

问题描述

mysql> select COUNT(*), * from help_keyword;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from help_keyword' at line 1

这不是:

mysql> select COUNT(*), help_keyword.* from help_keyword;
+----------+-----------------+------+
| COUNT(*) | help_keyword_id | name |
+----------+-----------------+------+
|      452 |               0 | JOIN |
+----------+-----------------+------+
1 row in set (0.00 sec)

那是为什么?

推荐答案

由于使用Count(*)，您告诉数据库您要进行聚合查询，因此select中任何不包含某些Aggregaton函数的列或表达式(例如count，min，max等)用于定义将对聚合执行的存储桶".列/表达式.通过包括表的所有列(*就是这样做的)，您说的是对表中的每一行进行聚合，这没有任何意义. (在任何适当的关系中，总是只有一行与该行的每个值匹配)

Because by using Count(*) you tell the database that you want an aggregation query, and therefore any columns or expressions in the select that do not include some aggregaton function (like count, min, max, etc.) are used to define the "buckets" that the aggregation will be performed on.. If there are any such expressions, you generally need to also include a Group By clause to explicitly list these columns/expressions. By including ALL the columns of the table, (which is what * does), you are saying aggregate over EVERY row in the table, which does not make sense. (In any proper relational tbale, there would always be only one row that matches every value for that row)

这篇关于在同一select子句中使用count(*)和*会产生错误.为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！