分页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.

任何帮助将不胜感激.

推荐答案

您可以使用the 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屋！