各位高手帮忙看看这条SQL语句，怎么改成不用exists的SQL？？100分送上

Question

SELECT 
   c3.* 
FROM 
   ZCCM0003 c3
WHERE 
   (   c3.ADJ_INVOICE is null 
    And 
       1 = (select 
               distinct 1 
            from 
               ZFIN0005 f5 
            where 
               f5.INVOICE_RKEY = c3.INVOICE_RKEY 
            and 
               f5.INVOICE_DATE <= to_date('2004-12-31','yyyy-mm-dd') 
            and 
               f5.INVOICE_TYPE = 'PA') 
    and 
       (   c3.INVOICE_NUMBER is null  
        or 
           1 = (select 
                   distinct 1 
                from 
                   ZCCM0001 
                where 
                   CH_INVOICE = c3.INVOICE_NUMBER 
                and 
                   CH_RCD_STATUS <> 8 
                and 
                   CH_DATE > to_date( '2004-12-31','yyyy-mm-dd' ))) 
    or 
       (   c3.ADJ_INVOICE is not null 
        and 
           1 = (select
                   distinct 1 
                from 
                   ZCCM0001 
                where 
                   CH_DATE <= to_date('2004-12-31','yyyy-mm-dd') 
                and 
                   CH_RCD_STATUS <> 8 
                and 
                   CH_INVOICE = c3.ADJ_INVOICE) 
        and 
           (   c3.INVOICE_NUMBER is null 
            or 
               1 =  (select 
                        distinct 1 
                     from 
                        ZCCM0001 
                     where 
                        CH_INVOICE = c3.INVOICE_NUMBER 
                     and 
                        CH_RCD_STATUS <> 8 
                     and 
                        CH_DATE > to_date('2004-12-31','yyyy-mm-dd')))))

Answer 1

libin_ftsafe(子陌红尘) :谢谢你的回复，你能解释一下你写的语句吗？我看不太懂，谢谢

Answer 2

给一个，效率不敢讲。
SELECT *
  FROM ZCCM0003 c3
 WHERE c3.ADJ_INVOICE is null
   and c3.INVOICE_RKEY in
       (select f5.INVOICE_RKEY
          from ZFIN0005 f5
         where f5.INVOICE_DATE <= to_date('2004-12-31', 'yyyy-mm-dd')
           and f5.INVOICE_TYPE = 'PA')
   and (c3.INVOICE_NUMBER is null or
       (c3.INVOICE_NUMBER in
       (select CH_INVOICE
            from ZCCM0001
           where CH_RCD_STATUS <> 8
             and CH_DATE > to_date('2004-12-31', 'yyyy-mm-dd'))))
    or (c3.ADJ_INVOICE is not null and
       (c3.ADJ_INVOICE in
       (select CH_INVOICE
            from ZCCM0001
           where CH_DATE <= to_date('2004-12-31', 'yyyy-mm-dd')
             and CH_RCD_STATUS <> 8)) and
       (c3.INVOICE_NUMBER is null or
       (c3.INVOICE_NUMBER in
       (select CH_INVOICE
             from ZCCM0001
            where CH_RCD_STATUS <> 8
              and CH_DATE > to_date('2004-12-31', 'yyyy-mm-dd')))))

Answer 3

为什么不用exists呢?它可以用 IN 来代替,但速度没有exists快.
如果慢可以写一个procedure来执行.

Answer 4

发现效率确实比exists的差很多,是10多秒跟几百秒的差别,有点恐怖

Answer 5

SELECT *
  FROM ZCCM0003 c3
 WHERE (c3.ADJ_INVOICE is null And c3.INVOICE_RKEY in(
        (select f5.INVOICE_RKEY
           from ZFIN0005 f5
          where f5.INVOICE_RKEY = c3.INVOICE_RKEY and
                f5.INVOICE_DATE <= to_date('2004-12-31', 'yyyy-mm-dd') and
                f5.INVOICE_TYPE = 'PA') and
        (c3.INVOICE_NUMBER is null or exists
         (select CH_INVOICE
            from ZCCM0001
           where CH_INVOICE = c3.INVOICE_NUMBER and CH_RCD_STATUS <> 8 and
                 CH_DATE > to_date('2004-12-31', 'yyyy-mm-dd'))) or
        (c3.ADJ_INVOICE is not null and exists
         (select CH_INVOICE
            from ZCCM0001
           where CH_DATE <= to_date('2004-12-31', 'yyyy-mm-dd') and
                 CH_RCD_STATUS <> 8 and CH_INVOICE = c3.ADJ_INVOICE) and
         (c3.INVOICE_NUMBER is null or exists
          (select CH_INVOICE
             from ZCCM0001
            where CH_INVOICE = c3.INVOICE_NUMBER and CH_RCD_STATUS <> 8 and
                  CH_DATE > to_date('2004-12-31', 'yyyy-mm-dd'))))))