大部分都要拼凑sql语句 效率从高到低如下
top+大于id
top+大于id
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
临时表 借助于连续性的id
CREATE Procedure GetAllArticles_Paged
(
@PageIndex int,
@PageSize int,
@TotalRecords out int,
@TotalPages out int
)
AS DECLARE @PageLowerBound int
DECLARE @PageUpperBound int -- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1 -- Create a temp table to store the select results
CREATE TABLE #tmp
(
RecNo int IDENTITY (1, 1) NOT NULL,
ArticleID int
) INSERT INTO #tmp
SELECT [ID]
FROM Articles
ORDER BY PubTime DESC SELECT A.*
FROM Articles A (nolock), #tmp T
WHERE A.ID = T.ArticleID AND
T.RecNo > @PageLowerBound AND
T.RecNo < @PageUpperBound
ORDER BY T.RecNo GO
(
@PageIndex int,
@PageSize int,
@TotalRecords out int,
@TotalPages out int
)
AS DECLARE @PageLowerBound int
DECLARE @PageUpperBound int -- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1 -- Create a temp table to store the select results
CREATE TABLE #tmp
(
RecNo int IDENTITY (1, 1) NOT NULL,
ArticleID int
) INSERT INTO #tmp
SELECT [ID]
FROM Articles
ORDER BY PubTime DESC SELECT A.*
FROM Articles A (nolock), #tmp T
WHERE A.ID = T.ArticleID AND
T.RecNo > @PageLowerBound AND
T.RecNo < @PageUpperBound
ORDER BY T.RecNo GO
top+not in id
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID
top+top
SELECT * FROM
(
SELECT TOP(PageSize) * FROM
(
SELECT TOP (PageSize * PageIndex) *
FROM Articles
ORDER BY PubTime DESC
)
ORDER BY PubTime ASC
)
ORDER BY PubTime DESC
(
SELECT TOP(PageSize) * FROM
(
SELECT TOP (PageSize * PageIndex) *
FROM Articles
ORDER BY PubTime DESC
)
ORDER BY PubTime ASC
)
ORDER BY PubTime DESC
游标
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
@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