演示代码如下:
select t.* from (
select otr_t.* from (
select *
from logs
where USERNAME is not null
AND MOTHOD in ('info','error','warn','debug')
order by CREATETIME DESC
) otr_t
) t where rownum between 10 and 20
结果查询结果为空,但实际上是有数据的。后猜想是rownum的问题,因为在otr_t以及t中查询,等于查询了两遍,而rownum作为默认的伪字段,在每次查询时都会重新生成,导致该查询结果为空。修改后的代码如下:
select t.* from (
select otr_t.*,rownum rn from (
select *
from logs
where USERNAME is not null
AND MOTHOD in ('info','error','warn','debug')
order by CREATETIME DESC
) otr_t
) t where rn between 10 and 20
解析:在查询otr_t时,将之伪字段rownunm存储为rn真实字段,然后在t中继续查询即可。