今天对分页语句做一个简单的总结，他们大同小异的，只要理解其中一个其他的就很好理解了。
使用top选项
select top 10 * from Orders
a where a.orderid not in ( select top 10 orderid from Orders order by orderid) order by a.orderid
使用max函数
这种方法的前提是有唯一值的一个列。
select top 10 * from Orders a
where 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
使用动态存SQL语句
一个分页存储过程，支持多表连接的情况。原理还是使用主键。
USE [PressErp]GO/****** Object: StoredProcedure [dbo].[UP_GetRecordByPageIndex] Script Date: 05/06/2013 22:00:50 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--用途：分页存储过程(对有主键的表效率极高) ALTER PROCEDURE [dbo].[UP_GetRecordByPageIndex] @tblName varchar(255), -- 表名 @fldName varchar(255), -- 主键字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)ASdeclare @strSQL varchar(6000) -- 主语句declare @strTmp varchar(100) -- 临时变量(查询条件过长时可能会出错，可修改100为1000)declare @strOrder varchar(400) -- 排序类型if @OrderType != 0begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName +'] desc'endelsebegin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc'endset @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrderif @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderif @PageIndex = 1begin set @strTmp ='' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + ']' + @strTmp + ' ' + @strOrderendif @IsReCount != 0 set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhereexec (@strSQL)
USE [Press]GO/****** Object: StoredProcedure [dbo].[UP_GetRecordByPage] Script Date: 09/16/2012 00:26:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--用途：支持任意排序的分页存储过程 CREATE PROCEDURE [dbo].[UP_GetRecordByPage]@tblName1 varchar(255), -- 主表名 School@tblName2 varchar(500), -- 次表以及连接School left join City on School.CityID=City.ID left join County on School.CountyID=County.ID@fldName varchar(255), -- 显示字段名 @OrderfldName varchar(255), -- 排序字段名,只能与一个排序字段名School.ID @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 1, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1000)='', -- 查询条件 (注意: 不要加 where) 'School.SchoolName like ''%浙江%'''@IsPrint bit=0 --是否打印ASdeclare @strSQL varchar(6000) -- 主语句 declare @strTmp varchar(1000) -- 临时变量(查询条件过长时可能会出错，可修改100为1000)declare @strOrder varchar(400) -- 排序类型if @OrderType != 0 begin set @strTmp = '<(select min' set @strOrder = ' order by ' + @OrderfldName +' desc' end else begin set @strTmp = '>(select max' set @strOrder = ' order by ' + @OrderfldName +' asc' endset @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ' + @tblName2 + ' where ' + @OrderfldName + ' ' + @strTmp + '( ' + replace(@OrderfldName,@tblName1,'tblTmp') + ' ) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @OrderfldName + ' from ' + @tblName2 + ' ' + @strOrder + ') as tblTmp)' + @strOrderif @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ' + @tblName2 + ' where ' + @OrderfldName + ' ' + @strTmp + '( ' + replace(@OrderfldName,@tblName1,'tblTmp') + ' ) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @OrderfldName + ' from ' + @tblName2 + ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) ' + @strOrderif @PageIndex = 1 begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ' + @tblName2 + ' ' + @strTmp + ' ' + @strOrder endif @IsReCount != 0 set @strSQL = @strSQL +'/*----*/'+' select count(1) as Total from ' + @tblName2 + ' 'if @IsPrint<>0 print (@strSQL)exec (@strSQL)GO