Oracle 和 MSSqlServer 分别使用多种方式实现分页查询

Oracle：

-- 使用row_number()分析函数
select *From (select row_number() over(order by orgid) as rn,t.* from taorg t) where rn between 11 and 20;
-- 使用rownum
select *from (select rownum rn, t.* From (select *From taorg order by orgid) t where rownum <=20) where rn > 10;
-- 使用rownum 和 between
select *from (select rownum rn,t.* From  (select *From taorg order by orgid) t) where rn between 11 and 20;

MSSqlServer :

-- row_number() 函数 不知道是不是模仿oracle  第11条到第20条
SELECT *FROM(SELECT *, row_number () OVER (ORDER BY ACCOUNT) AS rn FROM ORG_PERSON) as b WHERE rn BETWEEN 11 AND 20;
-- offset fetch next方式（SQL2012以上的版本才支持：推荐使用） 第11条到第20条
SELECT *FROM ORG_PERSON ORDER BY ACCOUNT OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
-- 老版本用 top not in 方式  第11条到第20条
SELECT TOP 10 *FROM ORG_PERSON WHERE ACCOUNT NOT IN (SELECT TOP 10 ACCOUNT FROM ORG_PERSON ORDER BY ACCOUNT ) ORDER BY ACCOUNT;