各類數(shù)據(jù)庫分頁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;