SELECT ROWNUM ROWNUM_,ROW_.evaluate_time , ROW_.*
FROM (select *
FROM scm_daily_query_tbl t
where 1 = 1
and company_cd = '2210'
and apply_status <> 99
order by evaluate_time desc
) ROW_;
排序结果:
当evaluate_time值相同时,如果对rownum伪列应用一个限定条件,如:where rownum<150,排序结果不同
SELECT ROWNUM ROWNUM_,ROW_.evaluate_time , ROW_.*
FROM (select *
FROM scm_daily_query_tbl t
where 1 = 1
and company_cd = '2210'
and apply_status <> 99
order by evaluate_time desc
) ROW_
WHERE ROWNUM <= 150
;
排序结果:
这样会导致分页异常,可以再加个唯一可区分的排序字段比如id:
SELECT ROWNUM ROWNUM_,ROW_.evaluate_time , ROW_.*
FROM (select *
FROM scm_daily_query_tbl t
where 1 = 1
and company_cd = '2210'
and apply_status <> 99
order by evaluate_time desc,id
) ROW_
WHERE ROWNUM <= 150
;