MySQL COUNT 与 LIMIT

MySQL COUNT with LIMIT

提问by Lee

What I want to do is SUM a column, but also COUNT the number of rows it is summing, with a limit of no more than 5 rows. So my query is:

我想要做的是 SUM 一列，但也要 COUNT 它求和的行数，限制不超过 5 行。所以我的查询是：

SELECT COUNT(*), SUM(score) FROM answers WHERE user=1 LIMIT 5

What I expected back was a COUNT(*) up to 5 (I cant just assume it will always be 5 in my code logic as it could have less than 5 answers), with a sum of the score of the up to5 rows.

我期望返回的是 COUNT(*) 最多 5（我不能假设它在我的代码逻辑中总是 5，因为它可能有少于 5 个答案），最多5 行的得分总和。

Instead what I seem to get back is the total number of matching rows (where user is 1) as the count, and the sum of the score for those rows. The numbers don't change whether I put LIMIT 1or LIMIT 5or even LIMIT 50.

相反，我似乎得到的是匹配行的总数（其中用户为 1）作为计数，以及这些行的分数总和。这些数字不会改变我是否把LIMIT 1或者LIMIT 5甚至是LIMIT 50。

What I believe will work in this situation is this instead

我相信在这种情况下会起作用的是这个

SELECT COUNT(*), SUM(score) FROM (SELECT * FROM answers WHERE user=1 LIMIT 5) AS a

But that seems a little convoluted for such a simple query, and as it's in a high traffic script, I want it to be as performant as possible.

但是对于这样一个简单的查询，这似乎有点令人费解，而且由于它在高流量脚本中，我希望它尽可能具有性能。

Am I missing something? I did find this bug reportfrom a few years back which seems to be related to this "problem", but I'm assuming it's not actually a bug?

我错过了什么吗？几年前我确实发现了这个错误报告，它似乎与这个“问题”有关，但我假设它实际上不是一个错误？

回答by Fabio

This is actually how your query works and is a normal behaviour. Using LIMITyou will not limit the count or sum but only the returned rows. So your query will return nrows as stated in your LIMITclause. And since your query actually returns only one row, applying a (non-zero) limit has no effect on the results.

这实际上是您的查询的工作方式，并且是正常行为。使用LIMIT您不会限制计数或总和，而只会限制返回的行。因此，您的查询将返回n您的LIMIT子句中所述的行。而且由于您的查询实际上只返回一行，因此应用（非零）限制对结果没有影响。

However, your second query will work as expected and is an established way of solving this problem.

但是，您的第二个查询将按预期工作，并且是解决此问题的既定方法。