之前说到那个MybatisPlus的分页查询效率问题，然后改成了Oracle_12新特性进行分页查询，然后分页失效了

select u.user_id, u.username, u.updated_at, ulc.level_name, ul.level_id, ul.level_sort 
from users u 
left join user_level ul on u.user_id = ul.user_id 
left join user_level_config ulc on ul.level_id = ulc.level_id 
order by ul.updated_at desc
offset 0 rows fetch next 10 rows only

这是查询首页，如果再下一页，就会是一样的数据

select u.user_id, u.username, u.updated_at, ulc.level_name, ul.level_id, ul.level_sort 
from users u 
left join user_level ul on u.user_id = ul.user_id 
left join user_level_config ulc on ul.level_id = ulc.level_id 
order by ul.updated_at desc
offset 10 rows fetch next 10 rows only

数据根本没有变化，所以这样分页是失效的。经过Google和排查，发现问题出在排序order by ul.updated_at desc上，因为关联表的关系，部分user在user_level中是没有数据的，所以按照ul.updated_at排序是会失效的，所以换成order by ul.updated_at,u.updated_at desc这样就会正常分页

select u.user_id, u.username, u.updated_at, ulc.level_name, ul.level_id, ul.level_sort 
from users u 
left join user_level ul on u.user_id = ul.user_id 
left join user_level_config ulc on ul.level_id = ulc.level_id 
order by ul.updated_at,u.updated_at desc
offset 10 rows fetch next 10 rows only

再尝试查询一次

select u.user_id, u.username, u.updated_at, ulc.level_name, ul.level_id, ul.level_sort 
from users u 
left join user_level ul on u.user_id = ul.user_id 
left join user_level_config ulc on ul.level_id = ulc.level_id 
order by ul.updated_at,u.updated_at desc
offset 20 rows fetch next 10 rows only

所以这个offset ? rows fetch next ? rows only Oracle_12新特性语法，在分页上失效跟排序有很大的关系。一般业务上的分页请求都是xxx.com/findList/{pageSize}/{page},所以写的通用点就是:

SELECT * FROM [TABLE_NAME] ORDER BY [FIELD] DESC OFFSET  ((#{page} - 1) * #{pageSize})  ROWS FETCH NEXT #{pageSize} ROWS ONLY