oracle 分页num,各数据库2种分页语法支持

原标题：各数据库2种分页语法支持

Mycat后端通过jdbc接入oracle 、 db2 、 sqlserver 、 postgresql等通过以下 2种方式支持分页语法

以防找不到可以先收藏哦！

1

数据库原生分页直接解析

应用端可以通过直接执行原生分页sql到mycat，各数据库原生分页支持如下：

oracle支持三层嵌套和row_number两种分页，以及rownum控制最大条数

select * from ( select row_.*, rownum rownum_ from ( select sid

from test where sts<>'N' order by sid desc ) row_ where rownum

<= 15) where rownum_ > 5;

SELECT *

FROM (SELECT sid, ROW_NUMBER() OVER (ORDER BY sid ) AS ROWNUM1

FROM test t

WHERE sts <> 'N'

) XX

WHERE ROWNUM1 > 5

AND ROWNUM1 <= 15;

SELECT * FROM (SELECT * FROM test t) XX WHERE ROWNUM <= 5;

db2支持rownumber分页和fetch first rows only语法

SELECT *

FROM (SELECT sid, ROW_NUMBER() OVER (ORDER BY sid ) AS ROWNUM

FROM test t

WHERE sts <> 'N'

) XX

WHERE ROWNUM > 5

AND ROWNUM <= 15;

SELECT sid

FROM test

ORDER BY sid desc

FETCH FIRST 15 ROWS ONLY;

Sqlserver支持row_number和row_number与top结合2种分页以及top限制最大条数

SELECT *

FROM (SELECT sid, ROW_NUMBER() OVER (ORDER BY sid DESC) AS ROWNUM

FROM test

WHERE sts <> 'N'

) XX

WHERE ROWNUM > 10

AND ROWNUM <= 25

select * from ( select row_number()over(order by tempColumn)tempRowNumber,* from ( select top 30 tempColumn=0, sid from test where sts<>'N' order by sid )t )tt where tempRowNumber>20;

postgresql分页

select sid from test order by sid desc limit 10 offset 5;

等价于mysql的

select sid from test order by sid desc limit 5,10;

其实mysql也兼容limit offset写法

2

Limit语法自动转换原生分页

select sid from test order by sid desc limit 5,10;

通过将标准的limit语法自动翻译转换为各数据库的原生分页，具体可以通过explain命令查看。返回搜狐，查看更多

责任编辑：