从表中计数，但在达到某个数字时停止计数
MySQL中有没有一种方法可以从表中获取COUNT(*)，如果数字大于x，它将在那里停止计数？基本上，我只想知道查询返回的记录数是否大于或小于特定数字。如果大于该数字，我并不关心有多少行，如果小于该数字，请告诉我计数。
我已经能够像这样捏造它：
-- let x be 100
SELECT COUNT(*) FROM (
SELECT `id` FROM `myTable`
WHERE myCriteria = 1
LIMIT 100
) AS temp
......但我想知道是否有一些方便的内置方法可以做到这一点？
感谢您的建议，但我应该更清楚这个问题背后的原因。它从几个连接的表中进行选择，每个表都有数千万条记录。使用索引条件运行 COUNT(*) 仍需要大约 80 秒，运行不带索引的条件大约需要 30 分钟左右。它更多的是关于优化查询而不是获得正确的输出。
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.
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?
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
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.
我想不出什么。在我看来，你正在做的事情完全达到了目的，而且 SQL 似乎并没有竭尽全力让你更容易、更简洁地完成这项工作。
考虑一下：您尝试做的事情在严格的集合算术上下文中没有意义。从数学上讲，答案是计算所有内容，然后将
MIN()设为 100，这正是您（务实且有充分理由）试图避免的情况。I can't think of anything. It looks to me like what you're doing exactly fulfils the purpose, and SQL certainly doesn't seem to go out of its way to make it easy for you to do this more succinctly.
Consider this: What you're trying to do doesn't make sense in a strict context of set arithmetic. Mathematically, the answer is to count everything and then take the
MIN()with 100, which is what you're (pragmatically and with good reason) trying to avoid.这有效：
更快（据我所知） 。
但并没有比 just:返回 5170965
This works:
but was not any faster (that I could tell) from just:
which returned 5170965.