請教此SQL語句是否可以優化?

Question

SELECT DISTINCT  '6AF' , c.IV_InvoiceType AS IV_InvoiceType  , c.IV_InvoiceSeq AS IV_InvoiceSeq ,  c.IV_Line AS IV_Line   ,   a.INVOICE_NO AS  INVOICE_NO
FROM GM_SALADDINVO_DTLB a , TB_Ginvoice b , TB_InvoiceLine c
WHERE a.INVOICE_NO IN ( SELECT FA_Sheet FROM TB_FactorySheet WHERE FA_Comp='6AF') 
AND CONVERT(CHAR(24),a.ADDINVOICE_NO)+CONVERT(CHAR(24),a.S_NUM) =CONVERT(CHAR(24),b.GI_No)+CONVERT(CHAR(24),b.GI_Seq)
AND a.S_SEQ=c.IV_Line
AND b.GI_Type=c.IV_InvoiceType AND b.GI_CreateSeq=c.IV_InvoiceSeq
AND b.GI_Comp=c.IV_Comp AND b.GI_Comp='6AF'
AND CONVERT(CHAR(1),c.IV_InvoiceType) + CONVERT(CHAR(13),c.IV_InvoiceSeq) + CONVERT(CHAR(4),c.IV_Line) + CONVERT(CHAR(24),a.INVOICE_NO)
NOT IN ( SELECT CONVERT(CHAR(1),IF_InvoiceType) + CONVERT(CHAR(13),IF_InvoiceSeq)+CONVERT(CHAR(4),IF_InvoiceLine) + CONVERT(CHAR(24),IF_FacSheet) FROM Tb_InvFacSheet WHERE IF_Comp='6AF')

Answer 1

SELECT DISTINCT  '6AF' , c.IV_InvoiceType AS IV_InvoiceType  , c.IV_InvoiceSeq AS IV_InvoiceSeq ,  c.IV_Line AS IV_Line   ,   a.INVOICE_NO AS  INVOICE_NO
FROM GM_SALADDINVO_DTLB a , TB_Ginvoice b , TB_InvoiceLine c
where exists (select 1 from TB_FactorySheet WHERE FA_Comp='6AF' and a.INVOICE_NO=FA_Sheet)
AND CONVERT(CHAR(24),a.ADDINVOICE_NO)+CONVERT(CHAR(24),a.S_NUM) =CONVERT(CHAR(24),b.GI_No)+CONVERT(CHAR(24),b.GI_Seq)
AND a.S_SEQ=c.IV_Line
AND b.GI_Type=c.IV_InvoiceType AND b.GI_CreateSeq=c.IV_InvoiceSeq
AND b.GI_Comp=c.IV_Comp AND b.GI_Comp='6AF'
AND not exists (select 1 from  Tb_InvFacSheet WHERE IF_Comp='6AF' and CONVERT(CHAR(1),c.IV_InvoiceType) + CONVERT(CHAR(13),c.IV_InvoiceSeq) + CONVERT(CHAR(4),c.IV_Line) + CONVERT(CHAR(24),a.INVOICE_NO)=CONVERT(CHAR(1),IF_InvoiceType) + CONVERT(CHAR(13),IF_InvoiceSeq)+CONVERT(CHAR(4),IF_InvoiceLine) + CONVERT(CHAR(24),IF_FacSheet))

Answer 2

LZ的辦法 , 3000多條數據時候是比之前的SQL快了1杪

Answer 3

NOT IN  ,in 变 exists