分页 SQL Server 2005 结果 [英] Paging SQL Server 2005 Results

问题描述

如何在 SQL Server 2005 中分页结果?

How do I page results in SQL Server 2005?

我在 SQL Server 2000 中尝试过，但没有可靠的方法来做到这一点.我现在想知道 SQL Server 2005 是否有任何内置方法?

I tried it in SQL Server 2000, but there was no reliable way to do this. I'm now wondering if SQL Server 2005 has any built in method?

我所说的分页是指，例如，如果我按用户名列出用户，我希望能够只返回前 10 条记录，然后是接下来的 10 条记录，依此类推.

What I mean by paging is, for example, if I list users by their username, I want to be able to only return the first 10 records, then the next 10 records and so on.

任何帮助将不胜感激.

推荐答案

您可以使用 Row_Number() 函数.其用法如下:

You can use the Row_Number() function. Its used as follows:

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

它将产生一个结果集，其中包含一个 RowID 字段，您可以使用该字段在它们之间进行分页.

From which it will yield a result set with a RowID field which you can use to page between.

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

等

这篇关于分页 SQL Server 2005 结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！