1.方法一
1 SELECT TOP 页大小 * 2 FROM table1 3 WHERE id NOT IN 4 ( 5 SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id 6 ) 7 ORDER BY id
2.方法二
1 --顺序写法: 2 SELECT TOP 页大小 * 3 FROM table1 4 WHERE id >= 5 ( 6 SELECT ISNULL(MAX(id),0) 7 FROM 8 ( 9 SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id 10 ) A 11 ) 12 ORDER BY id 13 14 --降序写法: 15 SELECT TOP 页大小 * 16 FROM table1 17 WHERE id <= 18 ( 19 SELECT ISNULL(MIN(id),0) 20 FROM 21 ( 22 SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id Desc 23 ) A 24 ) 25 ORDER BY id Desc
3.方法三
1 SELECT TOP 页大小 * 2 FROM 3 ( 4 SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1 5 ) A 6 WHERE RowNumber > 页大小*(页数-1)
分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句
分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句
分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用