从表中计数，但停止计数在某个数字 [英] Count from a table, but stop counting at a certain number

问题描述

如果数字大于 x COUNT（*） >，它会停止计数吗？基本上，我只想知道从查询返回的记录数是多少还是少于一个特定的数字。如果它超过这个数字，我真的不在乎有多少行，如果它少，告诉我的数量。

Is there a way in MySQL to COUNT(*) from a table where if the number is greater than x, it will stop counting there? Basically, I only want to know if the number of records returned from a query is more or less than a particular number. If it's more than that number, I don't really care how many rows there are, if it's less, tell me the count.

我已经能够fudge它像这样：

I've been able to fudge it like this:

-- let x be 100

SELECT COUNT(*) FROM (
    SELECT `id` FROM `myTable`
    WHERE myCriteria = 1
    LIMIT 100
) AS temp

...但我想知道是否有一些方便的内置方法来做到这一点？

...but I was wondering if there was some handy built-in way to do this?

感谢您的建议，但我应该更清楚地说明这个问题背后的原因。它从几个连接的表中选择，每个表都有数千万条记录。使用索引标准运行 COUNT（*）仍需要大约80秒，运行一个没有索引大约需要大约30分钟。

Thanks for the suggestions, but I should have been more clear about the reasons behind this question. It's selecting from a couple of joined tables, each with tens of millions of records. Running COUNT(*) using an indexed criteria still takes about 80 seconds, running one without an index takes about 30 minutes or so. It's more about optimising the query rather than getting the correct output.

推荐答案

SELECT * FROM WhateverTable WHERE WhateverCriteria
LIMIT 100, 1

LIMIT 100，1返回第101条记录是一个，否则没有记录。您可以使用上述查询作为EXIST子句中的子查询，如果有帮助的话。

LIMIT 100, 1 returns 101th record, if there is one, or no record otherwise. You might be able to use the above query as a sub-query in EXIST clauses, if that helps.

这篇关于从表中计数，但停止计数在某个数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！