用Mybatis框架执行的结果和自己执行的结果不一致。

[Lzz8658]

用Mybatis框架执行的结果和自己执行的结果不一致。

下面是Mybatis的配置

<select id="selectNoteByPage" parameterType="com.bean.Page" resultMap="NoteResultMap">
(select *
from (select * from bbs.bbsnote order by responsedate desc)
where rownum <![CDATA[<=]]> #{maxnum})
minus
(select *
from (select * from bbs.bbsnote order by responsedate desc)
where rownum <![CDATA[<=]]> #{minnum})
</select>

执行结果：
DEBUG [http-bio-8888-exec-1] - Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e69f6b0]
DEBUG [http-bio-8888-exec-1] - JDBC Connection [oracle.jdbc.driver.T4CConnection@19de40b4] will be managed by Spring
DEBUG [http-bio-8888-exec-1] - ==> Preparing: (select * from (select * from bbs.bbsnote order by responsedate desc) where rownum <= ?) minus (select * from (select * from bbs.bbsnote order by responsedate desc) where rownum <= ?)
DEBUG [http-bio-8888-exec-1] - ==> Parameters: 15(Integer), 10(Integer)
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 51(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 22
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 71(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 1
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 91(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 2
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 223(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 25
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 303(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 1
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 338(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 5
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 351(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 12
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 353(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 0
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 354(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 1
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 355(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 2
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 484(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 0
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 522(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 0
DEBUG [http-bio-8888-exec-1] - ====> Preparing: select * from BBS.BBSNOTESOFA where NOTEID=?
DEBUG [http-bio-8888-exec-1] - ====> Parameters: 540(Long)
DEBUG [http-bio-8888-exec-1] - <==== Total: 0
DEBUG [http-bio-8888-exec-1] - <== Total: 13

上面得到的结果不是我想要的，居然有13条数据，搞不懂这时为什么，但是我自己来执行sql，结果却是正确的，我真的找不出问题点了：

(select *
from (select * from bbs.bbsnote order by responsedate desc)
where rownum <=15)
minus
(select *
from (select * from bbs.bbsnote order by responsedate desc)
where rownum <=10);


这里执行结果是正确的，5条数据。

[卖水果的net]

select * from BBS.BBSNOTESOFA where NOTEID=? DEBUG [http-bio-8888-exec-1] - ====> Parameters: 540(Long) DEBUG [http-bio-8888-exec-1] - <==== Total: 0 DEBUG [http-bio-8888-exec-1] - <== Total: 13 这个语句，和你的语句并不一样；

[Lzz8658]

@wmxcn2000 这个不影响的，是一个结果集的1对多关联

[卖水果的net]

引用 2 楼 Lzz8658 的回复:

@wmxcn2000 这个不影响的，是一个结果集的1对多关联

两个不同的查询，要求结果一样？

[Lzz8658]

引用 3 楼 wmxcn2000 的回复:

[quote=引用 2 楼 Lzz8658 的回复:] @wmxcn2000 这个不影响的，是一个结果集的1对多关联

两个不同的查询，要求结果一样？[/quote] 这里是先查到BBSNOTE 表（帖子表）中的数据，然后再根据noteid查询该帖子下的所有楼层回复信息， 也就是说 上面的SQL先查到了13条数据在执行你说的这个sql，这样说可能不够准确，应该是BBSNOTE中每查到一条数据都会去找BBSNOTESOFA表中对应的该帖子的回复，然后生成一个完整的帖子对象

[Lzz8658]

应该是伪列的问题，难道是表的伪列和查询结果的伪列冲突了？？？ 昨晚莫名奇妙成功了，今天又变这样了

[卖水果的net]

你是先用这个 minus 语句，查出来了 5 条记录，再用这个 5 条记录的 ID 分别查询另一个表 SOFA ，本来应该出 5 条记录，结果出来了 13 条记录，是这个疑问吧？ 你 debug 一下，看看 minus 语句，是否返回正确。 如果用的是 eclipse ，可以先把项目清理一下。

[卖水果的net]

另外，这个分页语句，建议修改一下 ，效率欠佳；

select *
  from (select rownum, t.*
          from (select * from bbs.bbsnote order by responsedate desc) t
         where rownum <= 15)
 where rn > 10

[Lzz8658]

引用 7 楼 wmxcn2000 的回复:

另外，这个分页语句，建议修改一下 ，效率欠佳；

select *
  from (select rownum, t.*
          from (select * from bbs.bbsnote order by responsedate desc) t
         where rownum <= 15)
 where rn > 10

之前我就想过用between and来查询，但是有问题，当最小值不是1（例如 between 10 and 15）时就找不到任何数据，所以才用的minus，是数据库的问题吗（我用的Oracle10g），还是伪列的原因？ select * from (select * from bbs.bbsnote order by responsedate desc) where rownum between 10 and 15; 这sql查不到数据，你提供的也是。

[Lzz8658]

引用 8 楼 Lzz8658 的回复:

[quote=引用 7 楼 wmxcn2000 的回复:] 另外，这个分页语句，建议修改一下 ，效率欠佳；

select *
  from (select rownum, t.*
          from (select * from bbs.bbsnote order by responsedate desc) t
         where rownum <= 15)
 where rn > 10

之前我就想过用between and来查询，但是有问题，当最小值不是1（例如 between 10 and 15）时就找不到任何数据，所以才用的minus，是数据库的问题吗（我用的Oracle10g），还是伪列的原因？ select * from (select * from bbs.bbsnote order by responsedate desc) where rownum between 10 and 15; 这sql查不到数据，你提供的也是。 [/quote] 可以了，你提供的sql非常好！！ 刚才是rownum 没加别名 谢谢！

[Lzz8658]

原来rownum可以这样用！学到了，但目前的这个问题没能找到原因，只能等有时间再来研究了！