Oracle下用rownum进行分页时排序的错乱_oracle 加了rownum <=30后排序乱了

今天早上用户打电话说系统的排序有问题，仔细看了一下，问题好像是出在ROWNUM这个函数上。

在Oracle下ROWNUM函数是对查询结果按照顺序分配自增行序号，所以很多人都利用这个特点进行分页操作，即rownum between 30 and 60，这种限制返回行数的小窍门在普通情况下都很正常，但如果要对返回结果在进行排序就变得乱套了。

如下查询：

select page.* from
( select rownum page_id,
    id,
    unit_code,
    name,
    code
FROM lb_sys_twork page
WHERE state=1
ORDER BY unit_code,
    code) Page
where page.page_id between 10
       AND 31

这是一个网上很常用的查询模式，虽然我在子查询中加入了ORDER BY unit_code,code，并限制返回记录集的第10条到30条但是返回集并没有按照我所想的那样进行排序，查其原因发现rownum page_id这个伪字段并不是在最终返回结果集中生成，而是在引用它的子查询中已经返回，如此情况，在加了order by后返回的结果集也就不是按照1，2，3。。。这样的顺序排列了。我尝试将rownum page_id放在最外层，如下：

select rownum page_id, page.* from
( select id,
    unit_code,
    name,
    code
FROM lb_sys_twork page
WHERE state=1
ORDER BY unit_code,
    code) Page
where page.page_id between 10
       AND 31

这时查询出现错误，说是找不到Page_id这个字段，我将page_id的字段别名去掉，直接用rownum来做字段，结果集为空。到这时我才恍然大悟为什么把rownum叫做伪字段，rownum是在结果集进行输出时自动加入的一列，按照这种原理，如果你用Where rownum=10来限制返回的行数，将会得到的是一个空结果集，rownum>10等等也不行。看来把rownum放在子集还是放在最外层都不行，难道没办法了吗？

还是Google帮了忙找到了这片文章，其中介绍了MINUS这个减法集合运算符，MINUS是找到两个给定的数据集合之间的差异，即找到一个数据集合，该集合的数据是仅存在于前一个数据集而后一个数据集中不存在的，相当于集合1-集合2，利用此函数，我可以让集合1返回的是前50条数据，而集合2返回的是前30条，50-30就相当于返回的第30-50的数据。查询SQL如下：

SELECT rownum,
     page.*
FROM ( select Page.*
FROM lb_sys_twork page
WHERE page.state=1
ORDER BY unit_code,
    code) Page
WHERE rownum < 50 MINUS SELECT rownum,
     page.*
FROM ( select Page.*
FROM lb_sys_twork page
WHERE page.state=1
ORDER BY unit_code,
    code) Page
WHERE rownum < 30