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, @[email protected] output
select ceiling([email protected][email protected]) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)[email protected]+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