下面是我的sql语句SELECT DISTINCT *
FROM (SELECT DISTINCT A.*, B.*
FROM OCC_TN_AMA_081230 A, INC_TEL_LINE_NO B
WHERE A.OG_DIAL_NUM = B.TEL_LINE_NO
OR A.OG_DIAL_NUM = B.GATEWAY_NO
OR A.IC_CLG_NUM = B.TEL_LINE_NO
OR A.IC_CLG_NUM = B.GATEWAY_NO)
WHERE TEL_LINE_NO = NVL(DN, TEL_LINE_NO)
AND GATEWAY_NO = NVL(GN, GATEWAY_NO)
AND IP = NVL(ADDRESS, IP)
AND BUILD_CODE = NVL(ADDRESSCODE, BUILD_CODE)
AND IAD_NAME = NVL(IAD, IAD_NAME)
AND PORT = NVL(PORT, PORT)
AND ORIG_GREP = NVL(INTRUNKNUM, ORIG_GREP)
AND TERM_GREP = NVL(OUTTRUNKNUM, TERM_GREP)
AND ORIG_MEM = NVL(INTRUNKMEM, ORIG_MEM)
AND TERM_MEM = NVL(OUTTRUNKMEM, TERM_MEM);我的原意是这样,nvl(a,b)里,a是我输入的参数,我是想当我不输入参数时,可以忽略这个条件,例如是where 1=1.
但执行时,执行了N久也没结果。当我把AND ORIG_MEM = NVL(INTRUNKMEM, ORIG_MEM) AND TERM_MEM = NVL(OUTTRUNKMEM, TERM_MEM) 这两个条件注释掉后,
结果很快出来。但一加上后很慢,等了一小时也没有出来。请问这是什么原因呢?
FROM (SELECT DISTINCT A.*, B.*
FROM OCC_TN_AMA_081230 A, INC_TEL_LINE_NO B
WHERE A.OG_DIAL_NUM = B.TEL_LINE_NO
OR A.OG_DIAL_NUM = B.GATEWAY_NO
OR A.IC_CLG_NUM = B.TEL_LINE_NO
OR A.IC_CLG_NUM = B.GATEWAY_NO)
WHERE TEL_LINE_NO = NVL(DN, TEL_LINE_NO)
AND GATEWAY_NO = NVL(GN, GATEWAY_NO)
AND IP = NVL(ADDRESS, IP)
AND BUILD_CODE = NVL(ADDRESSCODE, BUILD_CODE)
AND IAD_NAME = NVL(IAD, IAD_NAME)
AND PORT = NVL(PORT, PORT)
AND ORIG_GREP = NVL(INTRUNKNUM, ORIG_GREP)
AND TERM_GREP = NVL(OUTTRUNKNUM, TERM_GREP)
AND ORIG_MEM = NVL(INTRUNKMEM, ORIG_MEM)
AND TERM_MEM = NVL(OUTTRUNKMEM, TERM_MEM);我的原意是这样,nvl(a,b)里,a是我输入的参数,我是想当我不输入参数时,可以忽略这个条件,例如是where 1=1.
但执行时,执行了N久也没结果。当我把AND ORIG_MEM = NVL(INTRUNKMEM, ORIG_MEM) AND TERM_MEM = NVL(OUTTRUNKMEM, TERM_MEM) 这两个条件注释掉后,
结果很快出来。但一加上后很慢,等了一小时也没有出来。请问这是什么原因呢?
还有,楼主用了两个distinct,检查下是否可以去掉一个?
distinct外面可以去掉一個,我忘了去掉.
請問二樓,這種情況有沒有解決辦法>?
同意,为什么用要两个distinct?而且是内外都用,用关键字distinct也会导致慢很多