SQL中分頁方法收集

末蓝、 2022-08-25 01:58 105阅读 0赞

1.有標識列時的方案:  
\--1.利用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 页大小\*(页数-1) id  
FROM 表  
ORDER BY id))  
ORDER BY ID  
\--2.利用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 页大小\*(页数-1) id  
FROM 表  
ORDER BY id) AS T))  
ORDER BY ID  
\--3.比較當前頁主鍵大小  
使用:不能指定分頁.只適用於 首頁,上一頁,下一頁,末頁.  
首页:  
select top 页大小 \* from 表名 order by 主键  
下一頁:  
select top 页大小 \* from 表名 where 主键 > 上一页末记录的主键 order by 主键  
上一頁:  
select top 页大小 \* from 表名 where 主键 > 上一页首记录的主键 order by 主键 desc  
末頁:  
select top 页大小 \* from 表名 order by 主键 desc

2.無標識列時的方案,同時也適用於有標識列時的情況.  
\--1.利用SQL的游标存储过程分页  
create procedure SqlPager  
@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

\--2.用临时表.  
select top 10 \* into \#tmp from categories  
select \* from \#tmp

3.SQL2005中的新的排序方法  
SELECT \*  
FROM  
(  
SELECT \*,ROW\_NUMBER() OVER (ORDER BY ItemID) AS RowNo  
FROM Portal\_Discussion  
) AS A  
WHERE RowNo>=1 and RowNo<=10