orcale 使用創建日期排序然後分頁每次取排序後的固定條數

需求:

     一個使用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條數據