orcale 使用创建日期排序然后分页每次取排序后的固定条数

墨蓝 2022-06-01 08:20 132阅读 0赞

需求:

一个使用mybatis分页插件的分页列表, 现在要求新增一条数据或者修改一条数据后,显示在最前端

思路:

使用sql , 先将查询出来的数据排序, 然后使用rownum > page\*size 和rownum <= (page+1)\*size 条件取固定条数的数据

问题 :

执行sql时遇到一个问题, 因为使用order by tn.create\_time desc 进行降序排列,是在where条件查询后, 所以rownum序号已经生成, 在排序rownum的值不会变, 改变的是数据顺序 ,

当使用rownum > page\*size 和rownum <= (page+1)\*size取值时其实取的就是没排序前的固定条数, 也就是说, order by虽然排序了, 可以取值没有取排完序后的数据

解决方法:

方法一:

SELECT *
    FROM ( SELECT tn.ID, ROW_NUMBER() OVER(PARTITION BY tn.valid ORDER BY NVL(tn.modify_time,tn.create_time) desc) RN FROM table_name tn JOIN table_name2 tn2 ON tn.ID = tn2.ID WHERE tn.condition = '条件' ) WHERE RN >(page*size) AND RN <= (page + 1)*size;

因为观察数据特点,发现存在一个标志可用\\不可用状态的字段, 所以使用 ROW\_NUMBER()  OVER (PARTITION BY COL1 ORDER BY COL2)  表示根据COL1分组，在分组内部根据 COL2排序,

根据可用状态分组,所有可用的就会进行编号,然后根据创建时间或者修改时间进行组内排序 ,又因为新增的数据没有修改时间,而新增后创建时间不变,只有修改时间改变,所以使用 NVL(tn.modify\_time,tn.create\_time) 判断,

如果修改时间为空(新增操作)就使用创建时间排序, 如果修改时间不为空(修改操作)那么就使用创建时间排序,  然后使用 WHERE RN >(page\*size) AND RN <= (page + 1)\*size 传入page(第几页)和size(每页长度)获取数据

方法二 :

select * from ( select row_.*, rownum rownum_ from ( select od.*,rownum from ( select *
          from table_name tn join table_name2 tn2 ON tn.ID = tn2.ID WHERE tn.condition = '条件' order by NVL(tn.modify_time,tn.create_time) DESC ) od ) row_ where rownum <= (page + 1)*size )where rownum_ > (page*size);

这是另一个思路, 首先将所有符合条件的数据根据创建时间进行排序,形成一张按创建时间排序的临时表,然后在给这张表添加上rownum序号,并取出 rownum <= (page + 1)\*size 条数据,

形成排序后的前 (page + 1)\*size 条临时表,最后取出需要的(page\*size)到(page + 1)\*size条数据