Sql-Server

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

  • February 10, 2015
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