SQL Server 2012:限制count(*)查询的最大行数

Is there any way to limit the maximum value for count(*), so it stops counting rows after it found N rows?

有没有办法限制count(*)的最大值，以便在找到N行后停止计数行?

I tried both SET ROWCOUNT N and TOP N, but they are ignored when I use SELECT count(*) FROM MyTable.

我尝试了设置ROWCOUNT N和TOP N，但是当我使用MyTable中的SELECT count(*)时，它们会被忽略。

3 个解决方案

#1

SELECT Count(*)
FROM (
   SELECT TOP(@MaxRowCount) *
   FROM MyTable
) as q

I think your WITH solution might not be performant because it has to iterate each row to assign ROW_NUMBER(). I believe the solution here will perform better.

我认为您的WITH解决方案可能不是performance，因为它必须迭代每一行来分配ROW_NUMBER()。我相信这里的解决方案会更好。

#2

Wouldnt you do that with an IF statement after you do a count?

难道你不会在数完之后再加上IF语句吗?

or maybe a subquery?

或子查询?

select count(*) as usercount from users where userid in (select top 5000 userid from users)

从用户id中选择count(*)作为usercount(从用户中选择top 5000 userid)

that would give you a potential usercount of 5000 or less.

这将使您的潜在用户数为5000或更少。

#3

I found a solution myself.

我自己找到了解决办法。

With OrderedRows AS(
    SELECT ROW_NUMBER() OVER (ORDER BY SomeColumn) AS RowNumber
    FROM MyTable
)
SELECT count(*) FROM OrderedRows WHERE RowNumber <= @MaxRowCount;

#1

SELECT Count(*)
FROM (
   SELECT TOP(@MaxRowCount) *
   FROM MyTable
) as q