一千萬個為什麽

搜索

替代sql NOT IN?

我想在Oracle中建立物化視圖(我是新手,順便說一句)。出於某種原因,它不喜歡其中存在子查詢。我一直在嘗試使用LEFT OUTER JOIN,但它現在返回不同的數據集。

簡而言之,這是我試圖修改的代碼:

SELECT *
FROM   table1 ros, table2 bal, table3 flx
WHERE  flx.name = 'XXX'
       AND flx.value = bal.value
       AND NVL (ros.ret, 'D') = Nvl (flx.attr16, 'D')
       AND ros.value = bal.segment3
       AND ros.type IN ( 'AL', 'AS', 'PL' )
       AND bal.period = 13
       AND bal.code NOT IN (SELECT bal1.code
                            FROM   table2 bal1
                            WHERE  bal1.value = flx.value
                                   AND bal1.segment3 = ros.value
                                   AND bal1.flag = bal.flag
                                   AND bal1.period = 12
                                   AND bal1.year = bal.year)

這是我的一個嘗試:

SELECT  *      
FROM   table1 ros, table2 bal, table3 flx
       LEFT OUTER JOIN table2 bal1
            ON bal.code = bal1.code      
WHERE  bal1.code is null
       AND bal1.segment3 = ros.value
       AND bal.segment3 = ros.value
       AND bal1.flag = bal.flag
       AND bal1.year = bal.year
       AND flx.name = 'XXX'
       AND flx.value = bal.value
       AND bal1.value = flx.value
       AND bal1.period_num = 12
       AND NVL (ros.type, 'D') = NVL (flx.attr16, 'D')
       AND ros.value = bal.segment3
       AND ros.type IN ( 'AL', 'AS', 'PL' )
       AND bal.period = 13;

這讓我瘋了!先謝謝您的幫助 :)

最佳答案

嘗試 NOT INNOT EXISTS

SELECT 
  *
FROM   
  table1 ros
  INNER JOIN table2 bal ON ros.value = bal.segment3
  INNER JOIN table3 flx ON flx.value = bal.value AND NVL(ros.ret, 'D') = Nvl(flx.attr16, 'D')
WHERE
  flx.name = 'XXX'
  AND ros.type IN ( 'AL', 'AS', 'PL' )
  AND bal.period = 13
  AND NOT EXISTS ( SELECT 1 FROM table2 WHERE
    code         = bal.code
    AND value    = flx.value
    AND segment3 = ros.value
    AND flag     = bal.flag
    AND period   = 12
    AND year     = bal.year
  )

BTW要快速進行子查詢,在 table2 上創建一個復合索引,其中包含您在子查詢中使用的所有字段。

轉載註明原文: 替代sql NOT IN?