[求助]oracle rownum的奇怪现象

Question

今天在系统中遇见一个BUG，有一个检索语句，是根据关键字检索的
select t2.* from 
(select rownum as t1_rownum, t1.* from 
(select a1.*,a2.*       
from
(
--all image info
    select  img.systemid,
            img.shootdate,
            img.copyright,
            img.copyrightname,
            img.humanrights,
            img.wirereport,
            img.dispatch,
            img.placead,
            img.title,
            img.caption,
            img.classific,
            img.keycomment,
            img.imageclass,
            img.senddate,
            img.dateofopen,
            img.format,
            (case when pubimg.publishflag='Y' then 'Y' else 'N' end) as publishflag
    from imagedata img
    left join
    (
     --all published real image
      select distinct a.systemid,'Y' as publishFlag
      from relatedimage a 
      where a.status='3'
            and a.stocktype in ('0','1')
    ) pubimg on (img.systemid = pubimg.systemid)
    where img.stocktype in ('0','1')
  AND (CONTAINS(img.TIXCHAR,'朝日') > 0)
  --AND (CONTAINS(img.TIXCHAR,'明石') > 0)
) a1
left join 
(
     --search publish image
     select distinct a.pageid,
            a.subpagecode,
            a.systemid as systemid2,
            pg.paperday,
            pg.medianame,
            pg.paperattr,
      pg.day,
            pg.pageright,
            pg.pagename,
            pg.editname,
            pg.editattribute,
            pg.localname
            --'Y' as pubsearchflag
     from relatedimage a
     left join subpagedat b on (a.pageid = b.pageid and a.subpagecode = b.subpagecode)
     left join pagedat pg on (a.pageid = pg.pageid)     
     where a.status = '3'
           and a.stocktype in ('0','1')
           and b.stocktype in ('0','1')
) a2 on (a1.systemid = a2.systemid2)ORDER BY PUBLISHFLAG DESC,SHOOTDATE DESC)t1)
t2 
where t1_rownum <=30 and t1_rownum >= 1如果关键字选择“明石”，则检索结果正确，这一点没有体现出rownum的奇怪问题；
但是，如果关键字选择“朝日”，在数据库中检索结果为0个，但是如果只执行蓝字部分，则检索结果正确。也就是说，为了增加rownum使原有检索结果发生了改变，另外我把rownum加到内层的select a1.*的前面就不会出错，这是一个什么奇怪现象？求助各位数据库高手，百分奉献，万分感谢。

Answer 1

自己顶一下, 期待高手对rownum问题的解答, 谢谢!

Answer 2

你把where t1_rownum <=30 and t1_rownum >= 1
去掉执行看看,是否能得到你想要的结果

Answer 3

可能问题不是出在rownum，可能和CONTAINS有关

Answer 4

这个仁兄说具体点，CONTAINS指什么

Answer 5

楼主用到了全文检索?CONTAINS用来针对全文检索的

Answer 6

select rownum as t1_rownum, t1.* from 
(select a1.*,a2.* 
from
(
--all image info
select img.systemid,
img.shootdate,
img.copyright,
img.copyrightname,
img.humanrights,
img.wirereport,
img.dispatch,
img.placead,
img.title,
img.caption,
img.classific,
img.keycomment,
img.imageclass,
img.senddate,
img.dateofopen,
img.format,
(case when pubimg.publishflag='Y' then 'Y' else 'N' end) as publishflag
from imagedata img
left join
(
--all published real image
select distinct a.systemid,'Y' as publishFlag
from relatedimage a 
where a.status='3'
and a.stocktype in ('0','1')
) pubimg on (img.systemid = pubimg.systemid)
where img.stocktype in ('0','1')
AND (CONTAINS(img.TIXCHAR,'朝日') > 0)
--AND (CONTAINS(img.TIXCHAR,'明石') > 0)
) a1
left join 
(
--search publish image
select distinct a.pageid,
a.subpagecode,
a.systemid as systemid2,
pg.paperday,
pg.medianame,
pg.paperattr,
pg.day,
pg.pageright,
pg.pagename,
pg.editname,
pg.editattribute,
pg.localname
--'Y' as pubsearchflag
from relatedimage a
left join subpagedat b on (a.pageid = b.pageid and a.subpagecode = b.subpagecode)
left join pagedat pg on (a.pageid = pg.pageid) 
where a.status = '3'
and a.stocktype in ('0','1')
and b.stocktype in ('0','1')
) a2 on (a1.systemid = a2.systemid2)ORDER BY PUBLISHFLAG DESC,SHOOTDATE DESC) t1什么结果

Answer 7

如果关键字选择“明石”，则检索结果正确，这一点没有体现出rownum的奇怪问题；
但是，如果关键字选择“朝日”，在数据库中检索结果为0个，但是如果只执行蓝字部分，则检索结果正确

Answer 8

真正的原因是rownum发生在order by之前，我用个例子来说明这个：SQL> select a.*,rownum from a;
 
        ID        ID1        NUM     ROWNUM
---------- ---------- ---------- ----------
         1          6          3          1
         1          4          3          2
         2          4          3          3
         3          5          3          4
         2          1          3          5
         3          2          3          6
         4          5          3          7
         5          7         10          8
         1          8          3          9
        11          5          4         10
 
10 rows selected
 
SQL> select a.*,rownum from a order by id;
 
        ID        ID1        NUM     ROWNUM
---------- ---------- ---------- ----------
         1          8          3          9
         1          4          3          2
         1          6          3          1
         2          4          3          3
         2          1          3          5
         3          2          3          6
         3          5          3          4
         4          5          3          7
         5          7         10          8
        11          5          4         10
 
10 rows selected
 
SQL> select a.*,rownum from (select * from a order by id) a;
 
        ID        ID1        NUM     ROWNUM
---------- ---------- ---------- ----------
         1          8          3          1
         1          4          3          2
         1          6          3          3
         2          4          3          4
         2          1          3          5
         3          2          3          6
         3          5          3          7
         4          5          3          8
         5          7         10          9
        11          5          4         10
 
10 rows selected
 
SQL> 

Answer 9

请问楼主是怎么解决的？我也遇到差不多的问题，红色部分的sql可以直接查询，外层的不加rownum可以查出结果，一旦加上就查不出来了楼上的我没有理解select barid,
               barUserName,
               barname,
               barregistrytime,
               lastlogintime,
               barstat,
               productmixtime,
               rownum rn,
               productmaxtime,
               agentname,
               amount,
               description
          from (select vbi.barid,
                       vbi.barUserName,
                       vbi.barname,
                       vbi.barregistrytime,
                       vbi.lastlogintime,
                       vbi.barstat,
                       vbi.productmixtime,
                       vbi.productmaxtime,
                       vbi.agentname,
                       tsum.amount,
                       tb.description
                  from v_bar_info vbi
                  left join (select tt.user_id, sum(ta.amount) amount
                              from tb_account_info tt
                              left join tb_account_detail ta on tt.accountid =
                                                                ta.accountid
                             group by tt.user_id) tsum on tsum.user_id =
                                                          vbi.barid
                  left join tb_bar tb on tb.userid = vbi.barid
                 where vbi.provinceid = 58
                   and vbi.cityid = 594
                   and vbi.offcieid =
                       (select tof.office_info_id
                          from tb_office tof
                         where tof.userid = 108519)
                 order by barid desc)