plsql 中的 rowid和rownum

一直以为rownum就是直接选出来排列的排序序号

但是今天在做一个优先排序的时候发现自己错了

select t1.* from ((SELECT DSCL_DATE_LIMIT, 0 AS STEP from DA_SET_CORP_LIMIT where DSCL_PATRON = '{0}' and DSCL_LOCATION_CD = '{1}') UNION (SELECT DSCL_DATE_LIMIT, 1 AS STEP from DA_SET_CORP_LIMIT where DSCL_PATRON is null and DSCL_LOCATION_CD = '{1}') UNION (SELECT DSCL_DATE_LIMIT, 2 AS STEP from DA_SET_CORP_LIMIT where DSCL_PATRON = '{0}' and DSCL_LOCATION_CD is null)) t1 WHERE rownum = 1 ORDER BY STEP;

这么一段sql我打算用step作为一个优先级来塞选  然后拿出存在的最高的那个

但是发现一直拿出来都是错的  而且是当三个优先级都有数据的时候拿到中间那个  it's weirdo！

后来找了资料才发现原来rownum是根据rowid来进行排序的  而rowid是根据插入的时间来生成的  也就是说我中间优先级的那条记录因为是最早插入的  结果rownum为1了

知道了就好办了  只要按排好的优先级重新生成一次就可以拿到我想要的第一条了

select * from (select * from ((SELECT DSCL_DATE_LIMIT, 0 AS STEP from DA_SET_CORP_LIMIT where DSCL_PATRON = '{0}' and DSCL_LOCATION_CD = '{1}') UNION (SELECT DSCL_DATE_LIMIT, 1 AS STEP from DA_SET_CORP_LIMIT where DSCL_PATRON is null and DSCL_LOCATION_CD = '{1}') UNION (SELECT DSCL_DATE_LIMIT, 2 AS STEP from DA_SET_CORP_LIMIT where DSCL_PATRON = '{0}' and DSCL_LOCATION_CD is null)) t1  ORDER BY STEP) t2 WHERE rownum = 1;