求sql分页语句 要讲得明白

求sql分页语句 要讲得明白

如下是一个倒序分页,正序把desc换成asc

select top 页面显示条数 *

from 表 where 条件 and 编号 not in

(select top ((第几页-1)*页面显示条数) 编号 from 表 where 条件 order by 编号 desc)

order by 编号 desc

　　CREATE PROCEDURE [dbo].[GetRecordSet]

　　@strSql varchar(8000),--查询sql,如select * from [user]

　　@PageIndex int,--查询当页号

　　@PageSize int--每页显示记录

　　AS

　　set nocount on

　　declare @p1 int

　　declare @currentPage int

　　set @currentPage = 0

　　declare @RowCount int

　　set @RowCount = 0

　　declare @PageCount int

　　set @PageCount = 0

　　exec sp_cursoropen @p1 output,@strSql,@scrollopt=1,@ccopt=1,@rowcount=@rowCount output --得到总记录数

　　select @PageCount=ceiling(1.0*@rowCount/@pagesize) --得到总页数

　　,@currentPage=(@PageIndex-1)*@PageSize+1

　　select @RowCount,@PageCount

　　exec sp_cursorfetch @p1,16,@currentPage,@PageSize

　　exec sp_cursorclose @p1

　　set nocount off

　　GO

select n.* from(select 字段名~~~ , rownum rn) n where rn between 从哪条 and 到哪条

select * from (select a.*,rownum row_num from (select * from mytable t order by t.id desc) a where rownum<=10 ) b where b.row_num >= 1

SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create PROC SP_pagination2 ( @sqlstr nvarchar(4000), --查询字符串 @pagecount int,--第N页 @pagesize int--每页行数 ) AS set nocount on declare @P1 int, --P1是游标的id @rowcount int exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output select @rowcount as 总行数,ceiling(1.0*@rowcount/@pagesize) as 页数,@pagecount as 当前页 set @pagecount=(@pagecount-1)*@pagesize+1 exec sp_cursorfetch @P1,16,@pagecount,@pagesize exec sp_cursorclose @P1 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

CREATE PROCEDURE [dbo].[pr_GetPLByPage] @tblName varchar(255), -- 表名 @fldName varchar(255), -- 主键字段名 @PageSize int = 20, -- 页尺寸 @PageIndex int = 1, -- 页码 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where) AS

declare @strSQL varchar(7000) -- 主语句 declare @strTmp varchar(1000) -- 临时变量 declare @strOrder varchar(400) -- 排序类型

if @OrderType != 0--指定排序 begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName +'] desc' end else--不排序 begin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc' end

set @strSQL = 'select top ' + cast(@PageSize as varchar(10)) + ' * from [' + @tblName + '] a where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + cast((@PageIndex-1)*@PageSize as varchar(10)) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder

if @strWhere != '' set @strSQL = 'select top ' + cast(@PageSize as varchar(10)) + ' * from [' + @tblName + '] a where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + cast((@PageIndex-1)*@PageSize as varchar(10)) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

if @PageIndex = 1 begin set @strTmp ='' if @strWhere != '' set @strTmp = ' where ' + @strWhere

set @strSQL = 'select top ' + cast(@PageSize as varchar(10)) + ' * from [' + @tblName + '] a' + @strTmp + ' ' + @strOrder end

if(@IsReCount!=0) --返回总数量，用来给前台提示页面总数时使用 begin set @strSQL = 'select count(1) as Total from [' + @tblName + '] ' if @strWhere != '' set @strSQL = @strSQL +' where ' + @strWhere end EXEC (@strSQL) return