插入数据：(2万条，用更多的数据测试会明显一些) SET IDENTITY_INSERT TestTable ON
declare @i int set @i=1 while @i<=20000 begin insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, ''''''''FirstName_XXX'''''''',''''''''LastName_XXX'''''''',''''''''Country_XXX'''''''',''''''''Note_XXX'''''''') set @i=@i+1 end
SET IDENTITY_INSERT TestTable OFF
-------------------------------------
分页方案一：(利用Not In和SELECT TOP分页) 语句形式： SELECT TOP 10 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 20 id FROM TestTable ORDER BY id)) ORDER BY ID
SELECT TOP 页大小 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id)) ORDER BY ID
-------------------------------------
分页方案二：(利用ID大于多少和SELECT TOP分页） 语句形式： SELECT TOP 10 * FROM TestTable WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP 20 id FROM TestTable ORDER BY id) AS T)) ORDER BY ID
SELECT TOP 页大小 * FROM TestTable WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id) AS T)) ORDER BY ID
-------------------------------------
分页方案三：(利用SQL的游标存储过程分页) create procedure XiaoZhengGe @sqlstr nvarchar(4000), --查询字符串 @currentpage 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 ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 set @currentpage=(@currentpage-1)*@pagesize+1 exec sp_cursorfetch @P1,16,@currentpage,@pagesize exec sp_cursorclose @P1 set nocount off
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
