我的sql文如下:
SELECT
N.NYUSHUKKA_MEISAI_ID, T.TANTOSHA_CD, S.SS_CD SHUKKASAKI_SS_CD
FROM
MRT_T_NYUSHUKKA_MEISAI N, MRT_M_SS_TANTOSHA T, MRT_M_SS S
WHERE
N.TANTOSHA_CD=T.TANTOSHA_CD(+)
AND N.SHUKKASAKI_SS_CD=S.SS_CD
AND N.SHUKKAMOTO_SS_CD=:SHUKKAMOTO_SS_CD
AND N.NYUSHUKKA_KBN='1'
AND N.DEL_FLG='0'
AND T.DEL_FLG='0'
AND S.DEL_FLG='0'
AND TO_CHAR(N.NYUSHUKKA_DATE,'YYYY/MM/DD') = :NYUSHUKKA_DATE
ORDER BY
N.NYUSHUKKA_DATE, N.SEIZO_NO, N.SHUKKASAKI_SS_CD DESC
原意是N.TANTOSHA_CD=T.TANTOSHA_CD(+) 进行左连接,同时T表的DEL_FLG必须为0,但是,现在在T表中存在DEL_FLG为0的数据,但是N.TANTOSHA_CD!=T.TANTOSHA_CD时,检索不到数据,将
AND T.DEL_FLG='0'这个检索条件删掉后就能正常检索出数据,请各位大虾解释下原因,谢谢!
SELECT
N.NYUSHUKKA_MEISAI_ID, T.TANTOSHA_CD, S.SS_CD SHUKKASAKI_SS_CD
FROM
MRT_T_NYUSHUKKA_MEISAI N, MRT_M_SS_TANTOSHA T, MRT_M_SS S
WHERE
N.TANTOSHA_CD=T.TANTOSHA_CD(+)
AND N.SHUKKASAKI_SS_CD=S.SS_CD
AND N.SHUKKAMOTO_SS_CD=:SHUKKAMOTO_SS_CD
AND N.NYUSHUKKA_KBN='1'
AND N.DEL_FLG='0'
AND T.DEL_FLG='0'
AND S.DEL_FLG='0'
AND TO_CHAR(N.NYUSHUKKA_DATE,'YYYY/MM/DD') = :NYUSHUKKA_DATE
ORDER BY
N.NYUSHUKKA_DATE, N.SEIZO_NO, N.SHUKKASAKI_SS_CD DESC
原意是N.TANTOSHA_CD=T.TANTOSHA_CD(+) 进行左连接,同时T表的DEL_FLG必须为0,但是,现在在T表中存在DEL_FLG为0的数据,但是N.TANTOSHA_CD!=T.TANTOSHA_CD时,检索不到数据,将
AND T.DEL_FLG='0'这个检索条件删掉后就能正常检索出数据,请各位大虾解释下原因,谢谢!
问题大概出在T.TANTOSHA_CD列有空值
如果N.NYUSHUKKA_DATE列有索引
TO_CHAR(N.NYUSHUKKA_DATE,'YYYY/MM/DD') = :NYUSHUKKA_DATE改成N.NYUSHUKKA_DATE - TO_DATE(:NYUSHUKKA_DATE,'YYYY/MM/DD') < 1更高效一些