分页的三种方式

时间：2014-09-09 15:21:29 阅读：145 评论：0 收藏：0 [点我收藏+]

分页的三种方式

临时表效率最差：

DECLARE @indextable table(id int identity(1,1) PRIMARY KEY,nid int);

INSERT INTO @indextable(nid) SELECT Id FROM FlightSpecialPrice AS l WITH (readpast) WHERE 1=1 ;--{0} {1};

SELECT recrowcount=@@ROWCOUNT;

SELECT * FROM @indextable as i

JOIN FlightSpecialPrice AS t

WITH (readpast) ON (i.nid = t.Id AND i.id > 20 AND i.id < 1000) ORDER BY i.Id;

Top 效率次于Row_Number:

SELECT TOP 1000 *

FROM FlightSpecialPrice

WHERE (ID NOT IN

(SELECT TOP 20 id

FROM FlightSpecialPrice

ORDER BY id))

ORDER BY ID

row_number() 效率最高：

select * from (

SELECT ROW_NUMBER() over(order by id ) num,*

FROM FlightSpecialPrice

) res

where num between 21 and 1020

原文：http://www.cnblogs.com/jzb-dev/p/3962354.html

踩

(0)

评论一句话评论（0）

分享档案

更多>