sql server分页

sql server基本分页方法

 

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

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

 

方法2：id>前几页id最大值

SELECT TOP 页大小*
FROM table1
WHERE id >
(
SELECT ISNULL(MAX(id),0)
FROM
(
SELECT TOP 页大小*(页数-1) id FROM table1ORDER BY id
) A
)
ORDER BY id

 

方法3：用两个top分别正序和倒序排列，共另个子查询来实现分页，

select * from ( 
      select top (pagesize) * from (
            select top (page*pagesize) * from Liwu_Items order by CreateTime desc
             ) 
      a order by CreateTime asc
      ) 
b order by CreateTime desc