ORACLE、DB2中rownum的使用

假设TABLE名为t，PRIMARY KEY为id，则：
1-5000000

SELECT t.*
FROM t , (SELECT id, ROW_NUMBER() OVER( ORDER BY id )  AS num FROM t) as t1
WHERE t.id = t1.id  AND t1.num <50000001

50000001 - 100000000

SELECT t.*
FROM t , (SELECT id, ROW_NUMBER() OVER( ORDER BY id )  AS num FROM t) as t1
WHERE t.id = t1.id  AND t1.num BETWEEN 50000001 AND 100000000

以此类推...

 

到于ORACLE，由于伪列rownum总是从1开始计数，所以当rownum 取>1的值时无效，如select * from xxx where rownum = 2，得不到结果。

可使用子查询的方式：select c.* from (select *, rownum as no from xxx) c where c.no = 2来得到结果。