left outer joinor right outer join次数过多出现的后果

    技术2022-05-11  35

    由于商业数据复杂,需要连接过多的表 ,居然出现了如下的情况。

     

    SQL如下:

    SELECT     T_KSEURIAGD.SRS_CDE  AS  SRS_CDE,    T_KSEURIAGD.KYT_CDE  AS  KYT_CDE,    T_KSEURIAGD.TKI_CDE  AS  TKI_CDE,    NVL(T_KSEURIAGD.USK_CDE, ' 00000 ' AS  USK_CDE,    T_KSEURIAGD.SHN_CDE  AS  SHN_CDE,    ( CASE   WHEN  ?  >=  T_KCMSHIREM.NAM_YKO_SDY  THEN         T_KCMSHIREM.DAI_SRS_NAM_SEI_NEW         ELSE  T_KCMSHIREM.DAI_SRS_NAM_SEI_OLD         END AS  DAI_SRS_NAM_SEI,    ( CASE   WHEN  ?  >=  C.KYT_JOH_YKO_SDY  THEN            C.KYT_NAM_RYK_NEW           ELSE         C.KYT_NAM_RYK_OLD         END AS   KYT_NAM_RYK,    ( CASE   WHEN  ?  >=  A.TKI_JOH_YKO_SDY  THEN            A.TKI_NAM_RYK_NEW           ELSE         A.TKI_NAM_RYK_OLD         END AS   TKI_NAM_RYK,        ( CASE   WHEN  ?  >=  B.TKI_JOH_YKO_SDY  THEN            B.TKI_NAM_RYK_NEW           ELSE         B.TKI_NAM_RYK_OLD         END AS   SRS_NAM_RYK,    T_KSEURIAGD.URI_KZY_YMD  AS  URI_KZY_YMD,T_KSEURIAGD.SHN_NAM_RYK_001  AS  SHN_NAM_RYK_001,T_KSEURIAGD.KIK  AS  KIK,T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ) AS  KSEURIAGD,T_KCMKUBUNM.KBN_NAM  AS  UNIT,T_KSEURIAGD.ZNU_NNY_TNK  AS  ZNU_NNY_TNK,$$$$T_KSEURIAGD.ZGN_KBN  AS  ZGN_KBN,T_KSEURIAGD.EIG_TAN_CDE  AS  EIG_TAN_CDE,T_KSEURIAGD.HDN_NUM  AS  HDN_NUM,T_KSEURIAGD.HDN_NUM_GNO  AS  HDN_NUM_GNO,T_KSEURIAGD.IRS  AS  IRS,T_KSEURIAGD.IRS_TNI  AS  IRS_TNI,T_KSEURIAGD.OYK_SIK_KBN  AS  OYK_SIK_KBN,T_KSEURIAGD.HSH_UMU_KBN  AS  HSH_UMU_KBN,KCMSYOHNM.MKR_SHN_NUM  AS  MKR_SHN_NUM,T_KSEURIAGD.HKI_KYT_CDE  AS  HKI_KYT_CDE,T_KSEURIAGD.HKI_KYT_CDE  AS  HKI_KYT_CDE,( CASE   WHEN  ?  >=  D.KYT_JOH_YKO_SDY  THEN            D.KYT_NAM_RYK_NEW           ELSE         D.KYT_NAM_RYK_OLD         END AS   KYT_NAM_RYK_DOH,T_KCMSIRSYM.CAT_NUM  AS  CAT_NUM,KCMSYJANM.JAN  AS  JAN_CDE,( CASE  T_KCMHSCALM.JHY_FRC_SHR_KBN  WHEN   1   THEN            ROUND (T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))) WHEN   2   THEN           CEIL(T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))) WHEN   3   THEN           TRUNC(T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))) WHEN   4   THEN      ( CASE  KCMTKYSNM.ZEI_FRC_KBN         WHEN   1   THEN            ROUND (T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))) WHEN   2   THEN           CEIL(T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))) WHEN   3   THEN           TRUNC(T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 )))          ELSE  T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))          END         )         ELSE  T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))         END AS   KIN,        ( CASE  T_KCMHSCALM.JHY_FRC_SHR_KBN  WHEN   1   THEN           TRUNC(T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.SIR_KZY_TNK_ARA  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 )), 3 )           ELSE          TRUNC(T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.SIR_KZY_TNK_ARA  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 )))         END AS   SRS_KIN,        ( CASE  T_KCMHSCALM.JHY_FRC_SHR_KBN  WHEN   1   THEN           TRUNC(($$) * (T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))), 3 )           ELSE  TRUNC(($$) * (T_KSEURIAGD.ZNU_NNY_TNK  * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))))           END ) AS   NNY_KIN,         ( CASE  T_KCMHSCALM.JHY_FRC_SHR_KBN  WHEN   1   THEN           TRUNC(($$)              * (T_KSEURIAGD.SIR_KZY_TNK_ARA  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 )), 3 )           ELSE          TRUNC(($$)             * (T_KSEURIAGD.SIR_KZY_TNK_ARA  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 )))         END AS   SRS_KZY_KIN,( CASE  T_KCMHSCALM.JHY_FRC_SHR_KBN  WHEN   1   THEN           TRUNC( ( CASE   WHEN  T_KSEURIAGD.HSH_UMU_KBN  =   1           THEN     ($$) * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))  -             ($$) * (T_KSEURIAGD.SIR_KZY_TNK_ARA  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))          ELSE   0          END ), 3 )         ELSE          TRUNC( ( CASE   WHEN  T_KSEURIAGD.HSH_UMU_KBN  =   1           THEN           ($$)  * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))  -             ($$)             * (T_KSEURIAGD.SIR_KZY_TNK_ARA  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))          ELSE   0          END ))         END ) AS   PRO_KIN,    (    ( CASE  T_KCMHSCALM.JHY_FRC_SHR_KBN  WHEN   1   THEN           TRUNC( ( CASE   WHEN  T_KSEURIAGD.HSH_UMU_KBN  =   1           THEN          ($$) * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))  -             ($$) * (T_KSEURIAGD.SIR_KZY_TNK_ARA  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))          ELSE   0          END ), 3 )         ELSE          TRUNC( ( CASE   WHEN  T_KSEURIAGD.HSH_UMU_KBN  =   1           THEN           ($$) * (T_KSEURIAGD.NNY_SUU_NSG  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))  -             ($$)             * (T_KSEURIAGD.SIR_KZY_TNK_ARA  *  DECODE(T_KSEURIAGD.ZGN_KBN, - 1 , - 1 , 1 ))          ELSE   0          END ))         END ) -         ( CASE  T_KCMHSCALM.JHY_FRC_SHR_KBN  WHEN   1   THEN         TRUNC((T_KSGHOSHOD.HSH_KES_GAK + T_KSGHOSHOD.KAJ_NKN), 3 )         ELSE         TRUNC(T_KSGHOSHOD.HSH_KES_GAK + T_KSGHOSHOD.KAJ_NKN)         END )        ) AS   KGA_KIN         FROM  T_KSEURIAGD LEFT   JOIN ( SELECT  ( CASE   WHEN  ?  <  T_KCMTKYSNM.ZEI_JOH_YKO_SDY  THEN      T_KCMTKYSNM.ZEI_FRC_KBN_OLD      ELSE     T_KCMTKYSNM.ZEI_FRC_KBN_NEW     END AS   ZEI_FRC_KBN,T_KCMTKYSNM.cor_cde,T_KCMTKYSNM.tki_ysn_bun_cde FROM  T_KCMTKYSNM)KCMTKYSNM  ON  (T_KSEURIAGD.cor_cde  =  KCMTKYSNM.cor_cde  AND  T_KSEURIAGD.tki_ysn_bun_cde  =  KCMTKYSNM.tki_ysn_bun_cde ) LEFT    JOIN  T_KCMSHIREM  ON (T_KSEURIAGD.COR_CDE  =  T_KCMSHIREM.COR_CDE   AND  T_KSEURIAGD.SRS_CDE  =  T_KCMSHIREM.SRS_CDE) LEFT    JOIN  T_KCMKYOTNM C  ON (T_KSEURIAGD.COR_CDE  =  C.COR_CDE   AND  T_KSEURIAGD.KYT_CDE  =  C.KYT_CDE) LEFT    JOIN  T_KCMTOKUIM A  ON (T_KSEURIAGD.COR_CDE  =  A.COR_CDE   AND  T_KSEURIAGD.TKI_CDE  =  A.TKI_CDE) LEFT    JOIN  T_KCMTOKUIM B  ON (T_KSEURIAGD.COR_CDE  =  B.COR_CDE   AND  T_KSEURIAGD.USK_CDE  =  B.TKI_CDE) LEFT    JOIN  T_KCMMAKERM  ON (T_KSEURIAGD.MKR_CDE  =  T_KCMMAKERM.MKR_CDE) LEFT    JOIN  T_KCMPRSELM  ON (T_KSEURIAGD.TKI_CDE  =  T_KCMPRSELM.TKI_CDE   AND  T_KSEURIAGD.USK_CDE  = T_KCMPRSELM.USK_CDE   AND  T_KSEURIAGD.SRS_CDE  = T_KCMPRSELM.SRS_CDE  AND  T_KSEURIAGD.SHN_CDE  = T_KCMPRSELM.SHN_CDE) LEFT    JOIN  T_KCMSIRSYM  ON (T_KSEURIAGD.COR_CDE  =  T_KCMSIRSYM.COR_CDE   AND  T_KSEURIAGD.SRS_CDE  =  T_KCMSIRSYM.SRS_CDE  AND  T_KSEURIAGD.SHN_CDE  =  T_KCMSIRSYM.SHN_CDE) LEFT    JOIN ( SELECT  T_KCMSYOHNM.shn_cde,T_KCMSYOHNM.TOK_SHN_KBN,T_KCMSYOHNM.MKR_SHN_NUM  FROM  T_KCMSYOHNM)KCMSYOHNM  ON (T_KSEURIAGD.SHN_CDE  =  KCMSYOHNM.SHN_CDE) LEFT    JOIN  T_KCMKYOTNM D  ON (T_KSEURIAGD.COR_CDE  =  D.COR_CDE   AND  T_KSEURIAGD.HKI_KYT_CDE  =  D.KYT_CDE) LEFT    JOIN  T_KSGHOSHOD  ON (T_KSEURIAGD.COR_CDE  =  T_KSGHOSHOD.COR_CDE   AND  T_KSEURIAGD.SRS_CDE  =  T_KSGHOSHOD.SRS_CDE  AND  T_KSEURIAGD.KYT_CDE  =  T_KSGHOSHOD.KYT_CDE AND  T_KSEURIAGD.TKI_CDE  =  T_KSGHOSHOD.TKI_CDE AND  T_KSEURIAGD.HDN_NUM  =  T_KSGHOSHOD.URI_DEN_NUM AND  T_KSEURIAGD.HDN_NUM_GNO  =  T_KSGHOSHOD.URI_DEN_GNO) INNER    JOIN  T_KCMKUBUNM  ON (T_KSEURIAGD.NNY_TNI_NSG  =  T_KCMKUBUNM.KBN_VAL) INNER   JOIN  T_KCMHSCALM  ON (T_KSEURIAGD.COR_CDE  =  T_KCMHSCALM.COR_CDE) LEFT   OUTER   JOIN  ( SELECT   T_KCMSYJANM.SHN_CDE,   MIN (SUBSTR(T_KCMSYJANM.JAN_CDE,  8 12 ))  AS  JAN FROM   T_KCMSYJANM WHERE   T_KCMSYJANM.CUR_FLG  =   1    AND  T_KCMSYJANM.DEL_FLG  =   0 GROUP   BY   T_KCMSYJANM.SHN_CDE UNION SELECT   T_KCMSYJANM.SHN_CDE,   MIN (SUBSTR(T_KCMSYJANM.JAN_CDE,  8 12 ))  AS  JAN FROM   T_KCMSYJANM WHERE   T_KCMSYJANM.CUR_FLG  =   0    AND  T_KCMSYJANM.DEL_FLG  =   0    AND  T_KCMSYJANM.SHN_CDE  NOT   IN  (     SELECT       T_KCMSYJANM.SHN_CDE     FROM       T_KCMSYJANM     WHERE       T_KCMSYJANM.CUR_FLG  =   1        AND  T_KCMSYJANM.DEL_FLG  =   0   ) GROUP   BY   T_KCMSYJANM.SHN_CDE) KCMSYJANM  ON  (T_KSEURIAGD.SHN_CDE  =  KCMSYJANM.SHN_CDE)   LEFT    JOIN  T_KCMKYSRSM  ON (T_KSEURIAGD.COR_CDE  =  T_KCMKYSRSM.COR_CDE   AND  T_KSEURIAGD.KYT_CDE  =  T_KCMKYSRSM.KYT_CDE    AND  T_KSEURIAGD.SRS_CDE  =  T_KCMKYSRSM.SRS_CDE   AND  T_KSEURIAGD.SHN_CDE  =  T_KCMKYSRSM.SHN_CDE) WHERE T_KCMHSCALM.COR_CDE  =  ? AND T_KSEURIAGD.SRS_CDE  =  T_KCMHSCALM.SRS_CDE AND  NVL(T_KSEURIAGD.MKR_CDE, ' 99999 ' =  T_KCMHSCALM.MKR_CDE  AND  T_KCMKUBUNM.DTB_ITM_NAM  =   ' TANI_NAME ' AND       T_KSGHOSHOD.DAT_KBN  =   1 AND   T_KSGHOSHOD.HSH_CHO_REN  =   0 AND   T_KCMHSCALM.CSV_SYT_KBN_SEL_JHY  =   1 AND   KCMSYOHNM.TOK_SHN_KBN  =   0   AND   T_KSEURIAGD.GET_TIS_YMN  =  ? AND  T_KSEURIAGD.TKI_YSN_BUN_CDE  NOT   IN  ( SELECT  T_KCMCNTRLM.INS_001  FROM  T_KCMCNTRLM WHERE  T_KCMCNTRLM.COR_CDE  =  ? AND  T_KCMCNTRLM.BSN_SYU  =   ' KM ' AND  T_KCMCNTRLM.CON_VAL_001  =   ' RP_TKI_YSN ' AND   T_KCMCNTRLM.CON_VAL_002  =   0 AND  T_KCMCNTRLM.CON_VAL_003  =   0 ) AND   T_KSEURIAGD.JUC_KBN  <>   14 AND T_KSEURIAGD.ISK_KBN  <>   1 AND  (T_KSEURIAGD.JSK_TKO_KBN  =   2    OR   T_KSEURIAGD.JSK_TKO_KBN  = 0 )$$ AND   T_KSEURIAGD.KYT_CDE  =  ? AND  T_KSEURIAGD.TKI_CDE  =  ? AND  T_KSEURIAGD.SRS_CDE  =  ? AND  T_KSEURIAGD.MKR_CDE  =  ? ORDER   BY T_KSEURIAGD.SRS_CDE,T_KSEURIAGD.KYT_CDE,T_KSEURIAGD.TKI_CDE,T_KSEURIAGD.USK_CDE,T_KSEURIAGD.URI_KZY_YMD,T_KSEURIAGD.USK_CDE,T_KSEURIAGD.SHN_CDE

    解决方案如下:将LEFT JOIN的次数减少,或将LEFT JOIN的表在作成一个子查寻。

    例如:

    LEFT OUTER JOIN 表名 ON()----〉LEFT OUTER JOIN 子查询 ON()

    为什么会出现这样的错误,本人认为是信息的堆叠造成信元重复,这样的错误在ORACLE10G版本中出现。

    2000中有无主键表一议,也就是说这种表有完全相同的重复信息,但是一些外界语言给与了屏蔽,相反造成了错误,

    ORACLE不予支持,微软的2005因该不会存在如此的问题吧!


    最新回复(0)