作為程序員來(lái)說(shuō)，與數(shù)據(jù)庫(kù)打交道是十分頻繁的分頁(yè)查詢是一個(gè)開(kāi)發(fā)者必須掌握的基本知識(shí)點(diǎn)，目前整理了下面三種SQLServer分頁(yè)查詢語(yǔ)句的寫(xiě)法，僅供參考。
一、Top Not IN 方式（查詢靠前的數(shù)據(jù)較快）
語(yǔ)法格式：
select top 條數(shù) * from tablename
where Id not in (select top pageSize*(pageIndex-1) Id from tablename)
示例：
SELECT TOP 2 * FROM Users WHERE Id NOT IN (SELECT TOP 2 Id FROM Users)
二、ROW_NUMBER() OVER()方式 （查詢靠后的數(shù)據(jù)速度較快）
語(yǔ)法格式：
SELECT * FROM (SELECT *,
ROW_NUMBER() OVER(Order by Id ) AS RowNumber from tablename ) as b
where RowNumber between pageIndex-1*pageSize and pageIndex*pageSize
示例：
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM Users ) as b
where RowNumber BETWEEN 0 and 5
三、offset fetch next方式 （速度更優(yōu)，限制Sql2012版本以上可以使用）
語(yǔ)法格式：
select * from tablename
order by Id offset pageIndex row fetch next pageSize row only
示例：
select * from Users order by Id offset 2 row fetch next 5 row only
四、分頁(yè)存儲(chǔ)過(guò)程
create PROCEDURE GetPageData
(
@TableName varchar(30),--表名稱
@IDName varchar(20),--表主鍵名稱
@PageIndex int,--當(dāng)前頁(yè)數(shù)
@PageSize int--每頁(yè)大小
)
AS
IF @PageIndex > 0
BEGIN
set nocount on
DECLARE @PageLowerBound int,@StartID int,@sql nvarchar(225)
SET @PageLowerBound = @PageSize * (@PageIndex-1)
IF @PageLowerBound<1
SET @PageLowerBound=1
SET ROWCOUNT @PageLowerBound
SET @sql=N'SELECT @StartID = ['+@IDName+'] FROM '+@TableName+' ORDER BY '+@IDName
exec sp_executesql @sql,N'@StartID int output',@StartID output
SET ROWCOUNT 0
SET @sql='select top '+str(@PageSize) +' * from '+@TableName+' where ['+@IDName+']>='+ str(@StartID) +' ORDER BY ['+@IDName+'] '
EXEC(@sql)
set nocount off
END
