带有LIMIT的MySQL COUNT [英] MySQL COUNT with LIMIT

问题描述

我想做的是对一列求和，但也要对它累加的行数进行计数，其最大限制为不超过5行.所以我的查询是:

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:

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

我期望返回的是最多5的COUNT(*)(我不能认为在我的代码逻辑中它将始终为5，因为它可能少于5个答案)，且总和为最多 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 to 5 rows.

相反，我似乎得到的是匹配行的总数(用户为1)作为计数，以及这些行的得分总和.无论我放LIMIT 1还是LIMIT 5甚至是LIMIT 50，数字都不会改变.

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 1 or LIMIT 5 or even 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 report from a few years back which seems to be related to this "problem", but I'm assuming it's not actually a bug?

推荐答案

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

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

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

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

这篇关于带有LIMIT的MySQL COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！