将 count(*) 作为选择查询的一部分是否错误
select id, first_name, count(*) from users;
users 表包含 10 个条目,但上面的 select 查询仅显示一行。将 count(*) 混合作为上述查询的一部分是否错误?
select id, first_name, count(*) from users;
The users table contains 10 entries, but the above select query shows only a single row. Is it wrong to mix count(*) as part of the above query?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(7)
COUNT 是一个聚合函数。您不能将其混合到正常查询中。
如果您想接收十个条目,只需执行正常的选择:
并获取条目数:
COUNT is a function that aggregates. You can't mix it into your normal query.
If you want to receive the ten entries just do a normal select:
and to get the number of entries:
这是因为您在查询的选择部分使用聚合函数,
要返回 10 条记录,您只需要查询中的 id 和 first_name 。
EG:
如果您想获取表中记录的计数,那么您可以使用
Its becuase you are using an aggregate function in the select part of the query,
to return the 10 records you just need the id, and first_name in the query.
EG:
if you wanted to get a count of the records in the table then you could use
这不是“错误”,但如果没有“group by”子句,它就毫无意义 - 大多数数据库都会拒绝该查询,因为如果您包含其他列,聚合函数应该包含group by。
It's not "wrong", but it is meaningless without a "group by" clause - most databases will reject that query, as aggregate functions should include a group by if you're including other columns.
不确定您到底想通过此实现什么目的?
将给出每个单独的用户和总数,但同样,不确定您为什么需要它。
Not sure exactly what you're trying to achieve with this?
will give each individual user and the total count but again, not sure why you would want it.
COUNT
是一个聚合函数,除非与group by结合使用,否则它将始终为您提供表中所有记录的计数。如果你将它与普通查询结合使用,那么它将优先决定最终输出,因为在你的情况下它返回 1。
如果你想返回所有 10 条记录,你应该写 -
COUNT
is an aggregate function and it will always give you count of all records in table unless used in combination with group by.If you use it in combination with normal query, then it will take priority in deciding the final output as in your case it returns 1.
If you want to return all 10 records, you should just write -
如果需要表中的行数,可以使用 MySQL 的 SQL_CALC_FOUND_ROWS 子句。检查 MySQL 文档以了解它是如何使用的。
If you need number of rows in a table, you can use MySQL's SQL_CALC_FOUND_ROWS clause. Check MySQL docs to see how it's used.