选择前n个,如果可以选择更多变量,则设置变量

选择51个结果,使用客户端层中的前50个,并使用计数来确定是否还有更多结果。

关于@dougs答案的旋转

SET NOCOUNT ON 

SELECT TOP 51 Field1, Field2, ... 
    into #t
    FROM MyTable WHERE ... --same expensive where clause

if @@rowcount > 50 
      SET @MoreExists = 1

 SET NOCOUNT OFF

 SELECT TOP 50 Field1, Field2, ... 
    from #t
    -- maintain ordering with an order by clause

对。

常用的方法是使用row_number():

WITH MyTableEntries AS 
( 
    SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row, col1, col2 
    FROM MyTable
    WHERE
          -- some expensive WHERE clause
)
SELECT col1, col2
FROM MyTableEntries 
WHERE Row BETWEEN(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

这里所展示的有效方法 SqlServercentral article :

DECLARE @startRow INT ; SET @startrow = 50
;WITH cols
AS
(
    SELECT table_name, column_name, 
        ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq, 
        ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
    FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq

使用count(*)结束…在子查询中?

DECLARE @ReqCount int;
SET @ReqCount = 50;

SELECT TOP (@ReqCount) *
FROM 
(
SELECT *, Count(*) OVER() AS TotalCnt
FROM MyTable WHERE ...
) t
ORDER BY ...
;

如果您还想使用row_number(),请尝试:

SELECT *
FROM 
(
SELECT *, ROW_NUMBER() OVER (ORDER BY ...) AS RowNum, Count(*) OVER() AS TotalCnt
FROM MyTable WHERE ...
) t
WHERE RowNum BETWEEN @StartRange AND @EndRange
ORDER BY ...
;

然后,您可以轻松地检查totalcnt>@reqcount(或@endrange),以查看是否还有更多要获取的内容。

抢劫