Orcale uses the creation date for sorting, and obtains the fixed number of items after sorting by page each time, orcale date sorting

Orcale uses the creation date for sorting, and obtains the fixed number of items after sorting by page each time, orcale date sorting

Requirements:

A paging list using the mybatis paging plug-in. You must add a new data entry or modify a new data entry.

Ideas:

Use SQL to sort the queried data first, and then use the rownum> page * size and rownum <= (page + 1) * size conditions to obtain a fixed number of data records.

Problem:

An error occurred while executing the SQL statement because order by tn was used. create_time desc is sorted in descending order. It is after the where condition query, so the rownum sequence number has been generated. The value of rownum in sorting does not change, but the data order is changed,

When rownum> page * size and rownum <= (page + 1) * size are used, the fixed number of rows before the order is not sorted. That is to say, although order by is sorted, no data in the sorted order is available.

Solution:

Method 1:

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 = 'condition') where rn> (page * size) and rn <= (page + 1) * size;

Because of the characteristics of the observed data, it is found that a flag is available \ unavailable, so ROW_NUMBER () OVER (partition by COL1 order by COL2) indicates grouping based on COL1, sort by COL2 within the group,

All available groups are numbered and sorted by creation time or modification time. Because the new data does not have a modification time, The creation time remains unchanged, only the modification time is changed, so NVL (tn. modify_time, tn. create_time) Judgment,

If the modification time is blank (new operation), The creation time is used for sorting. If the modification time is not blank (Modification Operation), The creation time is used for sorting, then, use where rn> (page * size) and rn <= (page + 1) * size to pass in the page (page number) AND size (page length) to obtain data.

Method 2:

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

This is another idea. First, sort all Qualified Data by creation time to form a temporary table sorted by creation time, and then add the rownum number to the table, and retrieve rownum <= (page + 1) * size data,

Form a temporary table (page + 1) * size after sorting, and finally retrieve the required (page * size) to (page + 1) * size data