mybatis mysql 执行同一条sql 结果不一样!

展开阅读全文

一条奇怪的SQL语句:在VB和DatabaseTour执行结果不一样(急用)

11-24

我在开发的程序中用到这条SQL语句: select BGQX,MJ,ND,QWH,TM,WJBH,WZ,ZRZ,ZTC,DH,DOC_STATE from D_DA2004,SYSTEM.DOCUMENT_TABLE where DH=DOC_INSTANCE_ID(+)rn其中:DH 属于 D_DA2004 ;DOC_INSTANCE_ID 属于DOCUMENT_TABLErn问题:rn1.当 DOCUMENT_TABLE 里没有记录的时候,用Database Tour中执行返回正确的记录项,用VB的ADODB打开 数据库执行该语句却返回0条记录rn2.当 DOCUMENT_TABLE 里有一条匹配记录的时候,用Database Tour中执行仍然正确,而用VB执行,则返回记录项的数量是无穷多,切只有一条正确记录rnrnD_DA2004内容:rnMJ WZ WJBH DZWJH QWH WB SJ ZTDW FLH FZ ND JH ZH XHBJ JYBJ FH QZH BGQX ZTC ZTGG ZTSL ZRZ TM DH SWH GH ZTLXrnrn绝密 命令 1文件1文件编号 1文件1全识标 正本 1文件1时间 页 1文件1分类号 1文件1附注 2004 1.00 0.00 1 1.00 00001 永久 1文件1主题词 1.00 1文件1责任者 1文件1题名 00001-Y-WS.W-2004-001-001 管代号 纸张rnrn机密 决定 1文件2文件编号 1文件2全识标 副本 1文件2时间 页 1文件2分类号 1文件2附注 2004 1.00 0.00 1 2.00 00001 永久 1文件2主题词 0.00 1文件2责任者 1文件2题名 00001-Y-WS.W-2004-001-002 管代号 纸张rnrnrn国内 通令 2文件1文件编号 副本 2文件1时间 页 2004 2.00 0.00 1 1.00 00001 永久 2文件1主题词 0.00 责任者 2文件1题名 00001-Y-WS.W-2004-002-001 案号 纸张rnrnrnDOCUMENT_TABLE,有记录时的内容:rnDOC_ID DOC_TYPE DOC_STATE DOC_INSTANCE_IDrn0+00001-Y-WS.W-2004-001-001 0.00 1.00 00001-Y-WS.W-2004-001-001rnrn此时执行的结果为rnBGQX MJ ND QWH TM WJBH WZ ZRZ ZTC DH DOC_STATErnrn永久 绝密 2004 1文件1全识标 1文件1题名 1文件1文件编号 命令 1文件1责任者 1文件1主题词 00001-Y-WS.W-2004-001-001 1.00rnrn永久 机密 2004 1文件2全识标 1文件2题名 1文件2文件编号 决定 1文件2责任者 1文件2主题词 00001-Y-WS.W-2004-001-002 rnrn永久 国内 2004 2文件1题名 2文件1文件编号 通令 责任者 2文件1主题词 00001-Y-WS.W-2004-002-001 rn 论坛

Mybatis执行与PLSQL执行结果不一样

04-03

sql语句如下:rn[code=sql]select * from ( select row_.*, rownum rownum_ from ( select A.COURSEDETAIL_ID as A_COURSEDETAIL_ID, A.COURSEDETAIL_YEAR as A_COURSEDETAIL_YEAR, A.COURSEDETAIL_TERM as A_COURSEDETAIL_TERM, A.COURSEDETAIL_LEVEL as A_COURSEDETAIL_LEVEL, A.COURSEDETAIL_THEORY_PERIOD as A_CTP, A.COURSEDETAIL_EXPRIMENT_PERIOD as A_CEP, A.COURSEDETAIL_CREDIT as A_COURSEDETAIL_CREDIT, A.COURSE_ID as A_COURSE_ID,A.COURSE_NAME as A_COURSE_NAME, A.COURSEDETAIL_START as A_COURSEDETAIL_START, A.COURSEDETAIL_END as A_COURSEDETAIL_END, A.COURSEDETAIL_DEADLINE as A_COURSEDETAIL_DEADLINE, A.COURSEDETAIL_DESCRIPTION as A_COURSEDETAIL_DESCRIPTION, A.COURSEDETAIL_LIMITCOUNT as A_COURSEDETAIL_LIMITCOUNT, A.COURSEDETAIL_TEACHWAY as A_COURSEDETAIL_TEACHWAY, A.PLANTEACHCONTENT_ID as A_PLANTEACHCONTENT_ID, A.COURSEDETAIL_STATUS as A_COURSEDETAIL_STATUS, A.DELETE_STATUS as A_DELETE_STATUS, A.CREATED_BY as A_CREATED_BY, A.CREATION_TIME as A_CREATION_TIME, A.MODIFIED_BY as A_MODIFIED_BY, A.MODIFICATION_TIME as A_MODIFICATION_TIME, A.TABLE_VER as A_TABLE_VER, B.CURRICULUM_ID as B_CURRICULUM_ID,B.CURRICULUM_NO as B_CURRICULUM_NO,B.CURRICULUM_NAME as B_CURRICULUM_NAME, B.CURRICULUM_TYPE1 as B_CURRICULUM_TYPE1,B.CURRICULUM_TYPE2 as B_CURRICULUM_TYPE2, C.PLANTEACHCONTENT_ID as C_PLANTEACHCONTENT_ID,C.COURSEDETAIL_ID as C_COURSEDETAIL_ID,C.PLANTEACHCONTENT_CONTENT C_PLANCONTENT, D.TEACHERCOURSE_ID as D_TEACHERCOURSE_ID,D.TEACHER_ID as D_TEACHER_ID,D.COURSEDETAIL_ID as D_COURSEDETAIL_ID, E.TEACHER_ID as E_TEACHER_ID,E.TEACHER_NAME as E_TEACHER_NAME from NCU.CS_COURSEDETAIL A left outer join NCU.ecm_curriculum B on A.COURSE_ID=B.CURRICULUM_ID left outer join NCU.cs_planteachcontent C on A.PLANTEACHCONTENT_ID=C.PLANTEACHCONTENT_ID left outer join NCU.cs_teachercourse D on D.COURSEDETAIL_ID=A.COURSEDETAIL_ID left outer join NCU.user_teacher E on D.TEACHER_ID=E.TEACHER_ID WHERE ( a.DELETE_STATUS = ? ) ) row_ where rownum <= (?+?) ) where rownum_ > ? [/code]rn服务器日志如下:rn[code=java]Connection - ooo Connection OpenedrnPreparedStatement - ==> Executing: select * from ( select row_.*, rownum rownum_ from ( select A.COURSEDETAIL_ID as A_COURSEDETAIL_ID, A.COURSEDETAIL_YEAR as A_COURSEDETAIL_YEAR, A.COURSEDETAIL_TERM as A_COURSEDETAIL_TERM, A.COURSEDETAIL_LEVEL as A_COURSEDETAIL_LEVEL, A.COURSEDETAIL_THEORY_PERIOD as A_CTP, A.COURSEDETAIL_EXPRIMENT_PERIOD as A_CEP, A.COURSEDETAIL_CREDIT as A_COURSEDETAIL_CREDIT, A.COURSE_ID as A_COURSE_ID,A.COURSE_NAME as A_COURSE_NAME, A.COURSEDETAIL_START as A_COURSEDETAIL_START, A.COURSEDETAIL_END as A_COURSEDETAIL_END, A.COURSEDETAIL_DEADLINE as A_COURSEDETAIL_DEADLINE, A.COURSEDETAIL_DESCRIPTION as A_COURSEDETAIL_DESCRIPTION, A.COURSEDETAIL_LIMITCOUNT as A_COURSEDETAIL_LIMITCOUNT, A.COURSEDETAIL_TEACHWAY as A_COURSEDETAIL_TEACHWAY, A.PLANTEACHCONTENT_ID as A_PLANTEACHCONTENT_ID, A.COURSEDETAIL_STATUS as A_COURSEDETAIL_STATUS, A.DELETE_STATUS as A_DELETE_STATUS, A.CREATED_BY as A_CREATED_BY, A.CREATION_TIME as A_CREATION_TIME, A.MODIFIED_BY as A_MODIFIED_BY, A.MODIFICATION_TIME as A_MODIFICATION_TIME, A.TABLE_VER as A_TABLE_VER, B.CURRICULUM_ID as B_CURRICULUM_ID,B.CURRICULUM_NO as B_CURRICULUM_NO,B.CURRICULUM_NAME as B_CURRICULUM_NAME, B.CURRICULUM_TYPE1 as B_CURRICULUM_TYPE1,B.CURRICULUM_TYPE2 as B_CURRICULUM_TYPE2, C.PLANTEACHCONTENT_ID as C_PLANTEACHCONTENT_ID,C.COURSEDETAIL_ID as C_COURSEDETAIL_ID,C.PLANTEACHCONTENT_CONTENT C_PLANCONTENT, D.TEACHERCOURSE_ID as D_TEACHERCOURSE_ID,D.TEACHER_ID as D_TEACHER_ID,D.COURSEDETAIL_ID as D_COURSEDETAIL_ID, E.TEACHER_ID as E_TEACHER_ID,E.TEACHER_NAME as E_TEACHER_NAME from NCU.CS_COURSEDETAIL A left outer join NCU.ecm_curriculum B on A.COURSE_ID=B.CURRICULUM_ID left outer join NCU.cs_planteachcontent C on A.PLANTEACHCONTENT_ID=C.PLANTEACHCONTENT_ID left outer join NCU.cs_teachercourse D on D.COURSEDETAIL_ID=A.COURSEDETAIL_ID left outer join NCU.user_teacher E on D.TEACHER_ID=E.TEACHER_ID WHERE ( a.DELETE_STATUS = ? ) ) row_ where rownum <= (?+?) ) where rownum_ > ? rnPreparedStatement - ==> Parameters: 0(String), 0(Integer), 20(Integer), 0(Integer)rnResultSet - <== Columns: A_COURSEDETAIL_ID, A_COURSEDETAIL_YEAR, A_COURSEDETAIL_TERM, A_COURSEDETAIL_LEVEL, A_CTP, A_CEP, A_COURSEDETAIL_CREDIT, A_COURSE_ID, A_COURSE_NAME, A_COURSEDETAIL_START, A_COURSEDETAIL_END, A_COURSEDETAIL_DEADLINE, A_COURSEDETAIL_DESCRIPTION, A_COURSEDETAIL_LIMITCOUNT, A_COURSEDETAIL_TEACHWAY, A_PLANTEACHCONTENT_ID, A_COURSEDETAIL_STATUS, A_DELETE_STATUS, A_CREATED_BY, A_CREATION_TIME, A_MODIFIED_BY, A_MODIFICATION_TIME, A_TABLE_VER, B_CURRICULUM_ID, B_CURRICULUM_NO, B_CURRICULUM_NAME, B_CURRICULUM_TYPE1, B_CURRICULUM_TYPE2, C_PLANTEACHCONTENT_ID, C_COURSEDETAIL_ID, C_PLANCONTENT, D_TEACHERCOURSE_ID, D_TEACHER_ID, D_COURSEDETAIL_ID, E_TEACHER_ID, E_TEACHER_NAME, ROWNUM_rnResultSet - <== Row: 1, 2013, two, 01, 36, 8, 2, 40, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 40, 03210D02, 仓储管理, compulsory, null, null, null, null, 14, 39, 1, 39, 史春乐, 1rnResultSet - <== Row: 2, 2013, two, 01, 56, 8, 4, 5, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 5, 03210101, 操作系统, compulsory, null, null, null, null, 20, 26, 2, 26, 乔道迹, 2rnResultSet - <== Row: 2, 2013, two, 01, 56, 8, 4, 5, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 5, 03210101, 操作系统, compulsory, null, null, null, null, 71, 29, 2, 29, 周海英, 3rnResultSet - <== Row: 3, 2013, two, 01, 36, 0, 2, 36, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 36, 03210C01, 网络经济学, compulsory, null, null, null, null, 63, 27, 3, 27, 苏贵影, 4rnResultSet - <== Row: 4, 2013, two, 01, 26, 8, 2, 48, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 48, 06060307, 算法分析与设计, compulsory, null, null, null, null, 38, 38, 4, 38, 师智斌, 5rnResultSet - <== Row: 5, 2013, two, 01, 60, 24, 5, 18, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 18, 03210138, .NET框架程序设计, compulsory, null, null, null, null, 12, 17, 5, 17, 李波, 6rnResultSet - <== Row: 6, 2013, two, 01, 68, 8, 4, 44, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 44, 03210M09, 通信原理, compulsory, null, null, null, null, 57, 28, 6, 28, 王伟, 7rnResultSet - <== Row: 7, 2013, two, 01, 36, 0, 2, 33, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 33, 03210B02, 人力资源管理, compulsory, null, null, null, null, 1, 32, 7, 32, 那鹏飞, 8rnResultSet - <== Row: 8, 2013, two, 01, 26, 16, 2, 6, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 6, 03210102, 人机交互的软件工程方法, compulsory, null, null, null, null, 75, 11, 8, 11, 唐菁, 9rnResultSet - <== Row: 9, 2013, two, 01, 40, 12, 3, 12, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 12, 03210129, 软件质量保证与测试, compulsory, null, null, null, null, 2, 19, 9, 19, 贾美丽, 10rnResultSet - <== Row: 9, 2013, two, 01, 40, 12, 3, 12, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 12, 03210129, 软件质量保证与测试, compulsory, null, null, null, null, 33, 22, 9, 22, 何志英, 11rnResultSet - <== Row: 10, 2013, two, 01, 64, 0, 4, 1, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 1, 01210115, 微观经济学, compulsory, null, null, null, null, 43, 48, 10, 48, 申丹虹, 12rnResultSet - <== Row: 11, 2013, two, 01, 44, 8, 3, 28, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 28, 03210159, 计算机网络, compulsory, null, null, null, null, 24, 37, 11, 37, 禹建, 13rnResultSet - <== Row: 11, 2013, two, 01, 44, 8, 3, 28, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 28, 03210159, 计算机网络, compulsory, null, null, null, null, 27, 33, 11, 33, 李旭妍, 14rnResultSet - <== Row: 11, 2013, two, 01, 44, 8, 3, 28, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 28, 03210159, 计算机网络, compulsory, null, null, null, null, 48, 44, 11, 44, 赵海雁, 15rnResultSet - <== Row: 11, 2013, two, 01, 44, 8, 3, 28, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 28, 03210159, 计算机网络, compulsory, null, null, null, null, 64, 40, 11, 40, 樊彩霞, 16rnResultSet - <== Row: 11, 2013, two, 01, 44, 8, 3, 28, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 28, 03210159, 计算机网络, compulsory, null, null, null, null, 69, 49, 11, 49, 王东, 17rnResultSet - <== Row: 12, 2013, two, 01, 60, 16, 4, 3, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 3, 02210108, 数据库原理及应用, compulsory, null, null, null, null, 6, 22, 12, 22, 何志英, 18rnResultSet - <== Row: 12, 2013, two, 01, 60, 16, 4, 3, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 3, 02210108, 数据库原理及应用, compulsory, null, null, null, null, 17, 3, 12, 3, 尹四清, 19rnResultSet - <== Row: 12, 2013, two, 01, 60, 16, 4, 3, null, null, null, null, null, null, null, null, 01, 0, null, 2014-04-01 18:53:22, null, null, 1, 3, 02210108, 数据库原理及应用, compulsory, null, null, null, null, 21, 23, 12, 23, 李玉蓉, 20rnDataSourceUtils - Returning JDBC Connection to DataSource[/code]rnrn但是我的分页是0-20那么就是20条结果,但是mybatis查处12条,但是直接PLSQL执行确是20条,何因啊? 论坛

没有更多推荐了,返回首页