編寫多個 CASE 語句的更有效方法

SELECT CASE WHEN ( Table.COLUMN1) IS NOT NULL THEN 1 ELSE 0 END,

 CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2) 
 IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y' 
 THEN 1 ELSE 0 END,

 CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2) 
 IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y' 
 THEN ( Table 1.FULL) END ) IS NOT NULL THEN 1 ELSE 0 END,

 CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2) 
 IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y' 
 THEN ( CASE WHEN Table 3.DO = 'Z' THEN 'Y' END ) END ) IS NOT NULL THEN 1 ELSE 0 END,


 CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2) 
 IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y' 
 THEN ( NVL(( Table2.COLUMN3), ( Table2.COLUMN3 )) ) END ) IS NOT NULL THEN 1 ELSE 0 END,


 CASE WHEN ( CASE WHEN ( CASE WHEN ( Table 4. COLUMN1) IS NOT NULL AND ( Table.COLUMN2) 
 IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y' 
 THEN ( NVL(( Table2.COLUMN3), ( Table2.COLUMN3)) ) END ) IS NOT NULL THEN 1 ELSE 0 END,


 CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2) 
 IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y' 
 THEN ( Table 3.FULL) END ) IS NOT NULL THEN 1 ELSE 0 END,


 CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2) 
 IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y' 
 THEN ( ( Table2.COLUMN3) ) END ) IS NOT NULL THEN 1 ELSE 0 END,


 CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2) 
 IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y' 
 then CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1 ) IS NOT NULL AND ( Table.COLUMN2) 
 IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y' 
 THEN ( ( Table2.COLUMN3) ) END ) IS NOT NULL AND ( Table.COLUMN1) - ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) 
 IS NOT NULL AND ( Table.COLUMN2) IS NOT NULL 
 THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y' 
 THEN ( ( Table2.COLUMN3) ) END ) < 90 THEN 'Y' ELSE 'N' END END ) = 'Y' THEN 1 ELSE 0 END,

有沒有更有效的方法來寫這個語句？

這是重寫查詢的一種方法，它應該在沒有嵌套 CASE 語句的情況下給出相同的結果。我首先檢查 NULL，以潛在地短路附加測試。我考慮使用單個 WHEN，每個表達式之間有一個 OR，但 OR 不是確定性的，所以這可能會更好。如果這些列大部分為空（相對於大部分已填充），您可以嘗試重寫它以測試 IS NOT NULL 而不是 IS NULL，並查看是否存在性能差異：

CASE 
   WHEN 
       Table.Column1 IS NULL
       OR Table.Column2 IS NULL
       OR Table1.Full IS NULL
   THEN 0
   WHEN
       Table.Column1 - Table.Column2 >= 180
   THEN 1
   ELSE 0
END,
CASE 
   WHEN 
       Table.Column1 IS NULL
       OR Table.Column2 IS NULL
   THEN 0
   WHEN
       Table.Column1 - Table.Column2 >= 180
       AND Table3.DO = 'Z'
   THEN 1
   ELSE 0
END,
CASE 
   WHEN 
       Table4.Column1 IS NULL
       OR Table.Column2 IS NULL
       OR Table2.COLUMN3 IS NULL
   THEN 0
   WHEN
       Table.Column1 - Table.Column2 >= 180
   THEN 1
   ELSE 0
END,
CASE 
   WHEN 
       Table.Column1 IS NULL
       OR Table.Column2 IS NULL
       OR Table3.Full IS NULL
   THEN 0
   WHEN
       Table.Column1 - Table.Column2 >= 180
   THEN 1
   ELSE 0
END,
...

我在這裡看到的是，您不斷檢查 Table.COLUMN1 是否不為空且 Table.COLUMN2 是否不為空，然後計算它們之間的差異，如果大於或等於 180，則返回“Y”你每次都把這整件事比作“Y”。同樣的模式重複 9 次。

1）你能把它作為表中的計算列嗎？假設您在其他查詢中也需要相同的公式，這可能會簡化很多事情。

2）如果這沒有幫助，您可以創建一個派生表或 CTE，其中包含您需要的表中的列 + 作為額外列：

CASE WHEN (Table.COLUMN1) IS NOT NULL AND (Table.COLUMN2) IS NOT NULL 
 THEN CASE WHEN (Table.COLUMN1) - (Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END
ELSE 'N' END

引用自：https://dba.stackexchange.com/questions/91533