各類(lèi)數(shù)據(jù)庫(kù)分頁(yè)SQL語(yǔ)法
2021-09-10 18:11:38

一. Mysql分頁(yè)SQL語(yǔ)法

收到客戶端{(lán)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分頁(yè)SQL語(yǔ)法

需要 order by 字段

收到客戶端{(lán)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分頁(yè)SQL語(yǔ)法

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

select * from table limit 10 offset 0;

四. Apache Impala分頁(yè)SQL語(yǔ)法

需要 order by 字段

收到客戶端{(lán)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分頁(yè)SQL語(yǔ)法

返回會(huì)多出一個(gè)字段row_id

收到客戶端{(lán)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分頁(yè)SQL語(yǔ)法

需要 order by 字段

收到客戶端{(lán)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;