分页的三种方式
分页的三种方式
临时表 效率最差：
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 IDrow_number() 效率最高：
select * from (
SELECT ROW_NUMBER() over(order by id ) num,*
FROM FlightSpecialPrice
) res
where num between 21 and 1020 
