[分页] SQL 数据分页

[分页] SQL 数据分页

1. 第一种，也是比较早接触的一种，利用Row_Number()函数对主键排序

--创建存储过程
Create Procedure Xmm_GetPageDate
(@pageIndex int,@pageSize int)
as
begin
with vitural_T as 
(
    select *,Row_Number() over(order by ID desc) as RowNumber 
    from TB_OrderLog
)
select * from vitural_T where RowNumber>(@pageIndex-1)*@pageSize and RowNumber<@pageSize
end
--执行
exec dbo.Xmm_GetPageDate 1,20

 

2. 第二种，用 Top + 主键 Not In 的方法

--创建存储过程
ALTER Procedure [dbo].[Xmm_GetPageDate1]
(@pageIndex int,@pageSize int)
as
begin
DECLARE @sqlStr nvarchar(4000)

SET @sqlStr=' SELECT TOP '+ CAST(@pageSize AS VARCHAR(20))+
' * FROM TB_OrderLog WHERE (ID NOT IN (SELECT TOP '+ CAST((@pageIndex-1)*@pageSize AS VARCHAR(20))+ ' ID FROM TB_OrderLog))' 

exec sp_ExecuteSql @sqlStr
end

--执行
exec dbo.Xmm_GetPageDate1 1,20  

 

3. 第三种是针对第二种的优化，我们看到 第二种的瓶颈在 Not In ，这可能会导致表扫描，改成如下形式，但是要求ID列连续

--创建存储过程
ALTER Procedure [dbo].[Xmm_GetPageDate2]
(@pageIndex int,@pageSize int)
as
begin
DECLARE @sqlStr nvarchar(4000)

SET @sqlStr=' 
SELECT TOP  '+ CAST(@pageSize AS VARCHAR(20))+' * FROM TB_OrderLog WHERE 
ID > (SELECT MAX(ID) FROM (SELECT  TOP '+ CAST((@pageIndex-1)*@pageSize AS VARCHAR(20))+ ' ID FROM TB_OrderLog ORDER BY ID)as T)
order by ID asc
' 

exec sp_ExecuteSql @sqlStr
end
--执行
exec dbo.Xmm_GetPageDate1 4,30  

 

4. 使用 OFFSET/FETCH NEXT 分页 （仅 2012 及以上版本可用）相较于以上分页方式，性能会提升不少

SELECT * FROM TB_OrderLog ORDER BY ID  DESC
OFFSET (@page -1) * @size ROWS FETCH NEXT @size ROWS ONLY;