转载地址:http://www.cnblogs.com/tylerdonet/archive/2011/06/01/2066071.html
使用top选项
select top 10 * from Ordersa where a.orderid not in ( select top 10 orderid from Orders order by orderid) order by a.orderid
使用max函数
这种方法的前提是有唯一值的一个列。
select top 10 * from Orders awhere a.orderid > ( select MAX (orderid) from ( select top 10 orderid from Orders order by orderid) as orderid)order by orderid
使用row_number()
select * from ( select ROW_NUMBER() over ( order by orderid) as rownumber, * from Orders) myresult where rownumber between 10 and 20 select top 10 * from ( select ROW_NUMBER() over ( order by orderid) as rownumber, * from Orders) myresult where rownumber > 10 with OrderedResult as ( select * ,ROW_NUMBER() over ( Order by orderid) as rownumber from Orders) select * from OrderedResult where rownumber between 10 and 20
使用rowcount设置
begin declare @first_id varchar ( 18 ), @startrow int set rowcount 10 select @first_id = orderid from Orders order by orderid select * from Orders where orderid > @first_id order by orderid set rowcount 0 end
使用临时表
begin declare @pagelowerbound int declare @pageupperbound int set @pagelowerbound = 10 set @pageupperbound = 20 create table #pageindex( [ indexid ] int identity ( 1 , 1 ) not null , [ id ] varchar ( 18 )) declare @sql nvarchar ( 2000 ) set @sql = ' insert into #pageindex([id]) select top ' + CONVERT ( nvarchar , @pageupperbound ) set @sql = @sql + ' orderid from Orders ' execute sp_executesql @sql select a. * from Orders a inner join #pageindex b on a.orderid = b.id where b.indexid > @pagelowerbound order by b.indexid drop table #pageindex end