各类数据库分页SQL语法

最新推荐文章于 2024-05-31 15:08:26 发布

zxfBdd

最新推荐文章于 2024-05-31 15:08:26 发布

阅读量1.7k

点赞数 3

分类专栏：大数据文章标签：数据库 oracle sql

原文链接：https://www.wangt.cc/2021/09/%E5%90%84%E7%B1%BB%E6%95%B0%E6%8D%AE%E5%BA%93%E5%88%86%E9%A1%B5sql%E8%AF%AD%E6%B3%95/

版权

大数据专栏收录该内容

595 篇文章 30 订阅

订阅专栏

一. 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 row_id
          from ( SELECT * FROM table_name ) tmp_page
        where rownum <= pageNo*pagesize)
where row_id > (pageNo-1)*pageSize;

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

六. 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;