当前位置: 代码迷 >> Sql Server >> 請教此SQL語句是否可以優化?该怎么处理
  详细解决方案

請教此SQL語句是否可以優化?该怎么处理

热度:340   发布时间:2016-04-27 16:34:14.0
請教此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))
  相关解决方案