分段查询

相比Oracle，PostgreSQL中分段查询的区别在以下几点：
1. 没有rownum项，而是用ROW_NUMBER() OVER()表示；
2. 子查询必须加别名，例如“as aa”，否则语法检查不通过；

另外分段查询的一个注意事项：rownum只能做“<”运算。如果写“>”、“>=”、“between”的时候，起始值只能是0或1。
因为rownum仅对查询结果排序生效，当写出“>1”时，第一条记录会被排除，然后第二条记录会变成第一条记录，接着第一条记录又被排除，……，所以查询会成功，但查询结果必定为空。务必注意这一点！！！

ORACLE

select column_name_a, rownum from table_name_a where column_name_a like 'condition%'; 
select column_name_a, no from (select column_name_a, rownum no from table_name_a where column_name_a like 'condition%') where no > 0 and no <=5;
select column_name_a, no from (select column_name_a, rownum no from table_name_a where column_name_a like 'condition%') where no > 5 and no <=10;
select column_name_a, no from (select column_name_a, rownum no from table_name_a where column_name_a like 'condition%') where no > 10 and no <=15;

PGSQL

select column_name_a, no from (select column_name_a, ROW_NUMBER() OVER() as no from table_name_a where column_name_a like 'condition%') as aa where no >0 and no <=5;
select column_name_a, no from (select column_name_a, ROW_NUMBER() OVER() as no from table_name_a where column_name_a like 'condition%') as aa where no >5 and no <=10;
select column_name_a, no from (select column_name_a, ROW_NUMBER() OVER() as no from table_name_a where column_name_a like 'condition%') as aa where no >10 and no <=15;

带排序功能的高效写法

ORACLE

select column_name_a from (select column_name_a, row_number() over (order by sal) no from table_name_a where column_name_a like 'condition%' order by column_name_a) where no > 0 and no <= 5;
select column_name_a from (select column_name_a, row_number() over (order by sal) no from table_name_a where column_name_a like 'condition%' order by column_name_a) where no > 5 and no <= 10;
select column_name_a from (select column_name_a, row_number() over (order by sal) no from table_name_a where column_name_a like 'condition%' order by column_name_a) where no > 10 and no <= 15;

PGSQL

select column_name_a from table_name_a where column_name_a like 'condition%' limit 5 offset 0;
select column_name_a from table_name_a where column_name_a like 'condition%' limit 5 offset 5;
select column_name_a from table_name_a where column_name_a like 'condition%' limit 5 offset 10;