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

问题描述

在 MySQL 中有没有办法从表中 COUNT(*) 如果数字大于 x，它会在那里停止计数?基本上，我只想知道从查询返回的记录数是多于还是少于特定数字.如果多于那个数字，我真的不在乎有多少行，如果少了，告诉我计数.

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?

感谢您的建议，但我应该更清楚这个问题背后的原因.它从几个连接的表中进行选择，每个表都有数千万条记录.使用索引标准运行 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屋！