SQL SERVER 2008 中分页方法集锦

    <span style="font-size: small">1.有标识列時的方案: <br />--1.利用Not In和SELECT TOP分页 <br />语句形式：&nbsp;&nbsp; <br />SELECT TOP 10 * <br />FROM TestTable <br />WHERE (ID NOT IN <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SELECT TOP 20 id <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM TestTable <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORDER BY id)) <br />ORDER BY ID <br /> <br /> <br />SELECT TOP 页大小 * <br />FROM TestTable <br />WHERE (ID NOT IN <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SELECT TOP 页大小*(页数-1) id <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM 表 <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORDER BY id)) <br />ORDER BY ID <br />--2.利用ID大于多少和SELECT TOP分页 <br />语句形式： <br />SELECT TOP 10 * <br />FROM TestTable <br />WHERE (ID &gt; <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SELECT MAX(id) <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM (SELECT TOP 20 id <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM TestTable <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORDER BY id) AS T)) <br />ORDER BY ID <br /> <br /> <br />SELECT TOP 页大小 * <br />FROM TestTable <br />WHERE (ID &gt; <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SELECT MAX(id) <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM (SELECT TOP 页大小*(页数-1) id <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM 表 <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORDER BY id) AS T)) <br />ORDER BY ID <br />--3.比較當前頁主鍵大小 <br />使用:不能指定分頁.只適用於 首頁,上一頁,下一頁,末頁. <br />首页: <br />select top 页大小 * from 表名 order by 主键 <br />下一頁: <br />select top 页大小 * from 表名 where 主键 &gt; 上一页末记录的主键 order by 主键 <br />上一頁: <br />select top 页大小 * from 表名 where 主键 &gt; 上一页首记录的主键 order by 主键 desc <br />末頁: <br />select top 页大小 * from 表名 order by 主键 desc <br /> <br />2.無標識列時的方案,同時也適用於有標識列時的情況. <br />--1.利用SQL的游标存储过程分页 <br />create procedure SqlPager <br />@sqlstr nvarchar(4000), --查询字符串 <br />@currentpage int, --第N页 <br />@pagesize int --每页行数 <br />as <br />set nocount on <br />declare @P1 int, --P1是游标的id <br />@rowcount int <br />exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output <br />select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 <br />set @currentpage=(@currentpage-1)*@pagesize+1 <br />exec sp_cursorfetch @P1,16,@currentpage,@pagesize <br />exec sp_cursorclose @P1 <br />set nocount off <br /> <br />--2.用临时表. <br />select top 10 * into #tmp from categories <br />select * from #tmp <br /> <br />3.SQL2005中的新的排序方法 <br />SELECT * <br />FROM <br />( <br />SELECT *,ROW_NUMBER() OVER (ORDER BY ItemID) AS RowNo <br />FROM Portal_Discussion <br />) AS A <br />WHERE RowNo&gt;=1 and RowNo&lt;=10</span>
 

评论

1 楼 GZQ0821 2012-02-01  

为啥不编译后贴出来呢！