解决方案

我们可以使用Row_Number()函数。
其用法如下：

SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName
FROM Users

从中将产生带有" RowID"字段的结果集，我们可以使用该字段在页面之间进行分页。

SELECT * 
FROM 
    ( SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName
      FROM Users 
    ) As RowResults
WHERE RowID Between 5 AND 10

方法2

当我需要分页时，通常也使用一个临时表。我们可以使用输出参数返回记录总数。 select中的case语句使我们可以对特定列上的数据进行排序，而无需求助于动态SQL。

--Declaration-

--Variables
@StartIndex INT,
@PageSize INT,
@SortColumn VARCHAR(50),
@SortDirection CHAR(3),
@Results INT OUTPUT

--Statements-
SELECT @Results = COUNT(ID) FROM Customers
WHERE FirstName LIKE '%a%'

SET @StartIndex = @StartIndex - 1 --Either do this here or in code, but be consistent
CREATE TABLE #Page(ROW INT IDENTITY(1,1) NOT NULL, id INT, sorting_1 SQL_VARIANT, sorting_2 SQL_VARIANT)
INSERT INTO #Page(ID, sorting_1, sorting_2)
SELECT TOP (@StartIndex + @PageSize)
    ID,
    CASE
        WHEN @SortColumn='FirstName' AND @SortDirection='ASC' THEN CAST(FirstName AS SQL_VARIANT)
        WHEN @SortColumn='LastName' AND @SortDirection='ASC' THEN CAST(LastName AS SQL_VARIANT)
        ELSE NULL
    END AS sort_1,
    CASE
        WHEN @SortColumn='FirstName' AND @SortDirection='DES' THEN CAST(FirstName AS SQL_VARIANT)
        WHEN @SortColumn='LastName' AND @SortDirection='DES' THEN CAST(LastName AS SQL_VARIANT)
        ELSE NULL
    END AS sort_2
FROM (
    SELECT
        CustomerId AS ID,
        FirstName,
        LastName
    FROM Customers
    WHERE
        FirstName LIKE '%a%'
) C
ORDER BY sort_1 ASC, sort_2 DESC, ID ASC;

SELECT
    ID,
    Customers.FirstName,
    Customers.LastName
FROM #Page
INNER JOIN Customers ON
    ID = Customers.CustomerId
WHERE ROW > @StartIndex AND ROW <= (@StartIndex + @PageSize)
ORDER BY ROW ASC

DROP TABLE #Page