Pagination In SQL Server 2005
SELECT ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNumber,* FROM Customers
SELECT TOP 2 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNumber,* FROM Customers) _myResults WHERE RowNumber > 2
SQL SERVER 2005分页时可能需要
WITH OrderedResults AS
(SELECT EntryName, ROW_NUMBER() OVER (order by EntryName) as RowNumber FROM tblEntry)
SELECT * FROM OrderedResults
WHERE RowNumber between 10 and 20
SQL SERVER 2000分页时可能需要
由于SQL SERVER 2000不支持ROW_NUMBER() 函数,所以只能这么查询
select identity(int,1,1) 'rownum',* into #temp from authors
select * from #temp where rownum>=2 and rownum<=5
drop table #temp
SQL SERVER 2000和2005 分页的通用解决方案:
select top m * from UserInfo where userID not in (select top n userID from UserInfo)
and userID like '%%' and userName like '%%'
说明:m表示每页显示的记录数,计算n的算法是:n=m*(页码-1)
ORACLE
select * from (select a.*,rownum num from(select * from
authors order by name desc) a where rownum<=20) where num>=10
010ad7c2-50fd-4970-b026-d16ce096567b|1|5.0
Tags: