SQL分页查询

iteye_3753

于 2011-12-16 13:43:00 发布

阅读量68

点赞数

方法1： 适用于 SQL Server 2000/2005

SELECT TOP 页大小 *
 FROM table1
 WHERE id NOT IN
           (
           SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
           )
 ORDER BY id

方法2：适用于 SQL Server 2000/2005

--顺序写法：
 SELECT TOP 页大小 *
 FROM table1
 WHERE id >=
 (
 SELECT ISNULL(MAX(id),0) 
 FROM 
 (
 SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id
 ) A
 )
 ORDER BY id
 
 --降序写法：
 SELECT TOP 页大小 *
 FROM table1
 WHERE id <=
 (
 SELECT ISNULL(MIN(id),0) 
 FROM 
 (
 SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id Desc
 ) A
 )
 ORDER BY id Desc