如何用联接条件重写查询NOT IN

NvR 发表于 Dev

录像机

以下查询成本高昂，无法正常使用（子查询）

select
    clm.column1,
    column2,
    ins.column3,
    dia.column4,
    clm.column5
From
    table1  clm 
    
    inner join table2 ins on clm.key = ins.key 
    
    left outer join table3 SFX ON
        clm.number = SFX.number
        and
        id in (
            select
                max(id)
            from
                table3
            group by number
        )
        AND
        clm.column1 NOT IN (
            sELECT
                column1
            FROM
                prod
        )
        and
        TO_CHAR( SFX.app_dt, 'YYYYMMDD' ) = to_char( '21-06-2020', 'YYYYMMDD' )
    
    left outer join (
        SELECT
            column1,
            RTRIM(
                XMLAGG( XMLELEMENT( E,MD.process, ',' ).EXTRACT( '//text()' ) ORDER BY column1 ).GetClobVal(), ','
            ) column4 
        FROM
            table4 D
            INNER JOIN table5 MD ON MD.key = D.id
        GROUP BY
            column1
    ) dia on clm.column1 = dia.column1 
where
    clm.column1 not in ( select column1 from prod );

我不知道如何AND clm.column1 NOT IN(sELECT column1 FROM prod)用加入条件重写。

如果有任何想法，请告诉我。

大力水手

您的查询中有多个问题。我尝试通过以下代码尽可能地解决它们：

select clm.column1, column2, ins.column3, dia.column4, clm.column5
From table1  clm 
     inner join table2 ins 
        on clm.key = ins.key 
     left outer join table3 SFX 
       ON clm.number = SFX.number
        -- DON'T USE SUB-QUERIES IN JOIN 
        -- ADDED IT IN THE WHERE CLAUSE
        --id in ( select max(id) from table3 group by number )
        -- WHY THIS SUBQUERY IS HERE. IT CAN BE IN LEFT JOIN OR WHERE 
        -- ADDED IN LEFT JOIN
        --AND clm.column1 NOT IN ( sELECT column1 FROM prod )
        --
        -- IF THERE IS INDEX ON SFX.app_dt THEN USE THE >= AND < AS FOLLOWS
        -- and TO_CHAR( SFX.app_dt, 'YYYYMMDD' ) = to_char( '21-06-2020', 'YYYYMMDD' )
        AND SFX.app_dt >= DATE '2020-06-21' AND SFX.app_dt < DATE '2020-06-22'
    left outer join (
        SELECT column1,
               RTRIM(
                XMLAGG( XMLELEMENT( E,MD.process, ',' ).EXTRACT( '//text()' ) 
                          ORDER BY column1 ).GetClobVal(), ','
            ) column4 
        FROM table4 D INNER JOIN table5 MD ON MD.key = D.id
        GROUP BY column1
    ) dia on clm.column1 = dia.column1 
    -- ADDED FOLLOWING LEFT JOIN
    LEFT JOIN PROD P ON P.COLUMN1 = CLM.COLUMN1
-- ADDED ENTIRE NEW WHERE CLAUSE
where P.COLUMN1 IS NULL
    AND (SFX.ID IS NULL OR SFX.id in ( select max(id) from table3 group by number) );

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。