以前我们用oracle写分页,嵌套了一层子查询,看起来很不优雅。oracle 12c出了新特性,让我们可以一行查询语句实现分页。废话不多说,直接上干货:
对下面sql进行分页,每页放10个表名,如何做?
SELECT TABLE_NAME as 表名 FROM ALL_TABLES WHERE OWNER = 'SYS' ORDER BY TABLE_NAME
直接使用如下代码:
SELECT TABLE_NAME as 表名 FROM ALL_TABLES WHERE OWNER = 'SYS' ORDER BY TABLE_NAME
offset 0 rows fetch first 10 rows only
直接解释 offset 0 rows fetch first 10 rows only是啥意思意思:
offset n rows 表示查询结果去掉前n行数据,我这里是 offset 0 rows,表示不去掉,从第一行开始;
fetch first n rows only 则表示抓取前面n条记录。
如果我是第1页,那么就是 offset 0 rows,一页大小是10,那么就是 offset 0 rows fetch first 10 rows only
如果我是第2页,那么就是 offset 10*(2-1) rows,一页大小是10,那么就是 offset 10*(2-1) rows fetch first 10 rows only
如果我是第3页,那么就是 offset 10*(3-1) rows,一页大小是10,那么就是 offset 10*(3-1) rows fetch first 10 rows only
以此类推:
如果我是第n页,那么就是 offset 10*(n-1) rows,一页大小是10,那么就是 offset 10*(n-1) rows fetch first 10 rows only
如何一页需要5行,则上面的10改成5即可。
总结:offset 0 rows fetch first 10 rows only 你只需要关注两个数字:0和10,其它关键字均不用变。
------------------------------------------额外知识-------------------------------------------
除了优雅舒服的分页功能,还可以按照数量取排序后的记录。
如我想把创建时间最新对应的表记录拿出来(假设表的最新记录数都是一条),以前我们都是这么干的:
select * from table where create_time = (select max(create_time) from table )
还居然还写子查询,看起来就很难受。如何改造?直接上干货:
select * from table order by create_time desc fetch first 1 rows only
先通过create time desc 把最新创建时间的记录放在最前面,然后直接使用 fetch first 1 rows only将最前面这一条记录抓取出来即可。
fetch first 1 rows only也很好记,符合英语语法。
有时候我们的实际需求比较变态,需要取创建时间第二新的记录,这种情况我也遇到过。这个时候直接改成:
select * from table order by create_time desc offset 1 rows fetch first 1 rows only
offset 1rows fetch first 1 rows only
表示先把第一条直接去掉,在抓一条。这样子就是第二条记录,也就是创建时间第二新的记录