分页 – Jennal's Blog

今天上csdn，看到有人在炫耀自己所谓的分页算法，呃。。。我觉得算不上是什么算法，就是一种解决方案吧。不过回帖的人好多，在回帖里淘到了不错的东西，发上来记录一下。

WITH OrderedOrders AS
(SELECT *,
ROW_NUMBER() OVER (order by [id])as RowNumber　　--id是用来排序的列
FROM table_info ) --table_info是表名
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;

WITH OrderedOrders AS

(SELECT *,

ROW_NUMBER() OVER (order by [id])as RowNumber　　--id是用来排序的列

FROM table_info ) --table_info是表名

SELECT *

FROM OrderedOrders

WHERE RowNumber between 50 and 60;

在windows server 2003, sql server 2005 CTP,P4 2.66GHZ,1GB 内存下测试，执行时间0秒，表中数据量230万
接下来大批量的数据查询性能瓶颈就在count了，不知道sql server 2005在这方面有没有什么改进.
附另一种方法:

SELECT *
FROM (select *,ROW_NUMBER() Over(order by id) as rowNum
from table_info ) as myTable
where rowNum between 50 and 60;

SELECT *

FROM (select *,ROW_NUMBER() Over(order by id) as rowNum

from table_info ) as myTable

where rowNum between 50 and 60;

Jennal's Blog

Jennal的技术博客

Tag: 分页

sql server 2005 分页