是否有更多的方法来改进这个查询？

Question

我一直在学习如何使用内部连接，所以我修改了下面的查询以使用内部连接，但是我在查询的最后一个片段上有问题，可以在那里使用内部连接吗？我有麻烦，因为"MOV“是在这个片段之前完成的查询.有人能帮我弄清楚怎么用吗？下面的查询工作正常，只是尽可能地改进它们

碎片：

FROM   TBLMOVOBLIGACIONES MOV1
                                  WHERE MOV1.STRMOVANOMES = :periodo
                                   AND  MOV1.STRCLINIT = MOV.STRCLINIT
                                   AND  MOV1.STROBLOBLIGSARC = MOV.STROBLOBLIGSARC

完整查询：

SELECT * FROM (

 

SELECT MOV.NUMPROCODIGO APLICATIVO,
       MOV.STRCLINIT NIT,
       CL.STRCLINOMBRE CLIENTE,
       MOV.STROBLOBLIGSARC OBLIGACION,
       MOV.NUMMOVVLRCAPCREDITO + MOV.NUMMOVVLRINTCREDI + MOV.NUMMOVVLRCAPOTRO SALDO_OBL,
       MOV.NUMMOVTIPOCREDITO TIPO_CREDITO,
       MOV.NUMMOVNRODIASCAP DIAS_CAP,
       MOV.NUMMOVNRODIASINT DIAS_INT,
       MOV.STRMOVCALIFEDAD,
       CASE 
        WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=0 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 30 THEN 'A'
        WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=31 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 90 THEN 'B'
        WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=91 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 180 THEN 'C'
        WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=181 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 360 THEN 'D'
        WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=361 THEN 'E'
       END AS CALIFEDAD_REAL
FROM TBLMOVOBLIGACIONES MOV INNER JOIN TBLCLIENTES CL ON MOV.STRCLINIT = CL.STRCLINIT 
WHERE MOV.STRMOVANOMES = :periodo
 AND  MOV.NUMPROCODIGO NOT IN (24)
 AND MOV.NUMMOVVLRCAPCREDITO + MOV.NUMMOVVLRINTCREDI + MOV.NUMMOVVLRCAPOTRO > 0
 AND  MOV.NUMMOVTIPOCREDITO = 1
 AND  NVL(MOV.STRMOVCALIFEDAD,'N') != (SELECT CASE 
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=0 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 30  THEN 'A'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=31 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 90 THEN 'B'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=91 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 180 THEN 'C'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=181 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 360 THEN 'D'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=361 THEN 'E'
                                     END AS CALIF_CALC
                              FROM   TBLMOVOBLIGACIONES MOV1
                              WHERE MOV1.STRMOVANOMES = :periodo
                               AND  MOV1.STRCLINIT = MOV.STRCLINIT
                               AND  MOV1.STROBLOBLIGSARC = MOV.STROBLOBLIGSARC)

Answer 1

我会质疑你的问题的这一部分：

 AND  NVL(MOV.STRMOVCALIFEDAD,'N') != (SELECT CASE 
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=0 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 30  THEN 'A'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=31 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 90 THEN 'B'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=91 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 180 THEN 'C'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=181 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 360 THEN 'D'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=361 THEN 'E'
                                     END AS CALIF_CALC
                              FROM   TBLMOVOBLIGACIONES MOV1
                              WHERE MOV1.STRMOVANOMES = :periodo
                               AND  MOV1.STRCLINIT = MOV.STRCLINIT
                               AND  MOV1.STROBLOBLIGSARC = MOV.STROBLOBLIGSARC)

要么(strmovanomes, strclinit, stroblobligsarc)包含(或者确切地说是) tblmovobligaciones的主键，要么它没有。

如果有，那么子查询将从您已经拥有的tblmovobligaciones中查询同一行。在这种情况下，应删除子查询，并将条件替换为：

 AND  NVL(MOV.STRMOVCALIFEDAD,'N') != CASE 
                                      WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=0 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 30  THEN 'A'
                                      WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=31 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 90 THEN 'B'
                                      WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=91 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 180 THEN 'C'
                                      WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=181 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 360 THEN 'D'
                                      WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=361 THEN 'E'
                                     END

如果没有，那么是什么阻止子查询返回多个行(这会在运行时导致错误)？

您也可以一次选择这个大表达式并重用它。这是优先考虑的问题。看起来是这样的：

SELECT MOV.NUMPROCODIGO APLICATIVO,
       MOV.STRCLINIT NIT,
       CL.STRCLINOMBRE CLIENTE,
       MOV.STROBLOBLIGSARC OBLIGACION,
       MOV.NUMMOVVLRCAPCREDITO + MOV.NUMMOVVLRINTCREDI + MOV.NUMMOVVLRCAPOTRO SALDO_OBL,
       MOV.NUMMOVTIPOCREDITO TIPO_CREDITO,
       MOV.NUMMOVNRODIASCAP DIAS_CAP,
       MOV.NUMMOVNRODIASINT DIAS_INT,
       MOV.STRMOVCALIFEDAD,
       mov.califedad_real_expr CALIFEDAD_REAL
FROM ( SELECT mov.*,
              CASE 
                WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=0 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 30 THEN 'A'
                WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=31 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 90 THEN 'B'
                WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=91 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 180 THEN 'C'
                WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=181 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 360 THEN 'D'
                WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=361 THEN 'E'
              END AS califedad_real_expr
        FROM TBLMOVOBLIGACIONES MOV) MOV 
INNER JOIN TBLCLIENTES CL ON MOV.STRCLINIT = CL.STRCLINIT 
WHERE MOV.STRMOVANOMES = :periodo
 AND  MOV.NUMPROCODIGO NOT IN (24)
 AND MOV.NUMMOVVLRCAPCREDITO + MOV.NUMMOVVLRINTCREDI + MOV.NUMMOVVLRCAPOTRO > 0
 AND  MOV.NUMMOVTIPOCREDITO = 1
 AND  NVL(MOV.STRMOVCALIFEDAD,'N') != mov.califedad_real_expr

(假设我认为子查询是不必要的，我是对的)