Sql-Server
編寫多個 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