一、TOP-MAX
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID
说明:
优点是:效率相对较高;
缺点是:这分页算法对标识列(主键或者其它排序列)的依赖很强,如果标识列有空值(NULL),分页不能成功。
二、TOP-NOT IN
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID
说明:
优点是:这分页算法对标识列(排序列)的没有依赖,如果标识列为空值(NULL),分页也能成功(依靠主键定位);
缺点是:效率相对较低。