請教此SQL語句是否可以優化?
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 ')
[解决办法]
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))
