分页方案一:(利用Not In和SELECT TOP分页) 语句形式: SELECTTOP10* FROM TestTable WHERE (ID NOTIN (SELECTTOP20 id FROM TestTable ORDERBY id)) ORDERBY ID SELECTTOP 页大小 * FROM TestTable WHERE (ID NOTIN (SELECTTOP 页大小*页数 id FROM 表 ORDERBY id)) ORDERBY ID 分页方案二: 语句形式: CreatePROCEDURE ListPage( @tblNamenvarchar(200), ----要显示的表或多个表的连接 @fldNamenvarchar(200) ='*', ----要显示的字段列表 @pageSizeint=10, ----每页显示的记录个数 @pageint=1, ----要显示那一页的记录 @pageCountint=1 output, ----查询结果分页后的总页数 @Countsint=1 output, ----查询到的记录数 @fldSortnvarchar(100) =null, ----排序字段列表或条件 @Sortbit=0, ----排序方法,0为升序,1为降序 @strConditionnvarchar(200) =null, ----查询条件,不需where @IDnvarchar(50) ----主表的主键 ) AS SET NOCOUNT ON Declare@sqlTmpnvarchar(1000) ----存放动态生成的SQL语句 Declare@strTmpnvarchar(1000) ----存放取得查询结果总数的查询语句 Declare@strIDnvarchar(1000) ----存放取得查询开头或结尾ID的查询语句 Declare@sqlSortnvarchar(200) ----存放临时生成的排序条件 Declare@intCountsint----要移动的记录数 Declare@BeginIDint----开始的ID Declare@EndIDint----结束的ID --------首先生成排序方法--------- if@Sort=0--升序 begin ifnot(@fldSortisnull) set@sqlSort=' Order by '+@fldSort else set@sqlSort=' Order by '+@ID end else--降序 begin ifnot(@fldSortisnull) set@sqlSort=' Order by '+@fldSort+' DESC' else set@sqlSort=' Order by '+@ID+' DESC ' end --------生成查询语句-------- --此处@strTmp为取得查询结果数量的语句 if@strConditionisnull--没有设置显示条件 begin set@sqlTmp=@fldName+' From '+@tblName set@strTmp='select @Counts=Count('+@ID+') FROM '+@tblName set@strID=' From '+@tblName end else begin set@sqlTmp=+@fldName+'From '+@tblName+' where '+@strCondition set@strTmp='select @Counts=Count('+@ID+') FROM '+@tblName+' where '+@strCondition set@strID=' From '+@tblName+' where '+@strCondition end ----取得查询结果总数量----- exec sp_executesql @strTmp,N'@Counts int out ',@Counts out --取得分页总数 if@Counts<=@pageSize set@pageCount=1 else set@pageCount= (@Counts/@pageSize) +1 --计算要移动的记录数 if@page=1 set@intCounts=@pageSize else begin set@intCounts= (@page-1) *@pageSize+1 end -----取得分页后此页的第一条记录的ID set@strID='select @BeginID='+@ID+''+@strID set@intCounts=@intCounts-@pageSize+1 setrowcount@intCounts exec sp_executesql @strID,N'@BeginID int out ',@BeginID out -----取得分页后此页的最后一条记录的ID set@intCounts=@intCounts+@pageSize-1 print@intCounts setrowcount@intCounts exec sp_executesql @strID,N'@BeginID int out ',@EndID out ------恢复系统设置----- setrowcount0 SET NOCOUNT OFF ------返回查询结果----- if@strConditionisnull set@strTmp='select '+@sqlTmp+' where '+@ID+' between '+str(@BeginID) +' and '+str(@EndID) else set@strTmp='select '+@sqlTmp+' where '+@ID+' (between '+str(@BeginID) +' and '+str(@EndID) +') and '+@strCondition ifnot(@sqlSortisnull) set@strTmp=@strTmp+@sqlSort exec sp_executesql @strTmp GO 分页方案三: 语句形式: createPROCEDURE sy_more_cs ( @modeint=0, @pagesizeint=10, @pageindexint=1 ) AS SET NOCOUNT ON if@mode=0 begin selectcount(*) as 记录数 from dbo.house_info_cs; end else begin declare@indextabletable(id intidentity(1,1),nid int) declare@PageLowerBoundint declare@PageUpperBoundint set@PageLowerBound=(@pageindex-1)*@pagesize set@PageUpperBound=@PageLowerBound+@pagesize insertinto@indextable(nid) select[id]from dbo.house_info_cs orderby dt desc; select ls.*from dbo.house_info_cs ls,@indextable t where ls.id=t.nid and t.id>@PageLowerBoundand t.id<=@PageUpperBoundorderby t.id end
分页方案一:(利用Not In和SELECT TOP分页)语句形式:SELECT TOP 10 *FROM TestTableWHERE (ID NOT IN (SELECT TOP 20 id FROM TestTable ORDER BY id))ORDER BY ID SELECT TOP 页大小 *FROM TestTableWH