各类数据库分页SQL语法
2021-09-10 18:11:38

一. Mysql分页SQL语法

收到客户端{pageNo:1,pagesize:10}
select * from table limit (pageNo-1)*pageSize, pageSize;
select * from table limit pageSize offset (pageNo-1)*pageSize;

select * from table limit 0, 10;
select * from table limit 10 offset 0;

二. Apache Doris分页SQL语法

需要 order by 字段

收到客户端{pageNo:1,pagesize:10}
select * from table order by ID limit pageSize offset (pageNo-1)*pageSize;

select * from table order by ID limit 10 offset 0;

三. PostgreSql分页SQL语法

收到客户端{pageNo:1,pagesize:10}
select * from table limit pageSize offset (pageNo-1)*pageSize;

select * from table limit 10 offset 0;

四. Apache Impala分页SQL语法

需要 order by 字段

收到客户端{pageNo:1,pagesize:10}
select * from table order by ID limit pageSize offset (pageNo-1)*pageSize;

select * from table order by ID limit 10 offset 0;

五. Oracle分页SQL语法

返回会多出一个字段row_id

收到客户端{pageNo:1,pagesize:10}
select *
  from (select tmp_page.*, rownum def_row_id
          from ( SELECT * FROM table_name ) tmp_page
        where rownum <= pageNo*pagesize)
where def_row_id > (pageNo-1)*pageSize;

select *
  from (select tmp_page.*, rownum def_row_id
          from ( SELECT * FROM table_name ) tmp_page
        where rownum <= 10)
where def_row_id > 0;

六. Teradata分页SQL语法

需要 order by 字段

收到客户端{pageNo:1,pagesize:10}
select * from table_name
qualify row_number() over(order by id) > (pageNo-1)*pageSize and row_number() over(order by id) <= pageNo*pagesize;

select * from table_name
qualify row_number() over(order by id) > 0 and row_number() over(order by id) <= 10;