在 WHERE 子句的 CASE 表達式中使用 IS NULL

我有一個WHERE要在其中使用CASE表達式的子句。但是，我的CASE表達式需要檢查欄位是否為IS NULL.

如果@UserRole變數 value = ‘Analyst’，則SupervisorApprovedBy列值必須為NULL。否則，我是說返回所有數據，即SupervisorApprovedBy = SupervisorApprovedBy。

我需要更改以下內容嗎？

WHERE SupervisorApprovedBy = 
CASE 
   WHEN @UserRole = 'Analyst' THEN NULL
   ELSE SupervisorApprovedBy
END

我不能=用於NULL. 經測試，不起作用。

我希望所有行都返回，包括那些 where SupervisorApprovedBy IS NOT NULL。

我認為你讓它變得比應該的更難。

如果@UserRole 是“分析員”，那麼 SupervisorApprovedBy 也應該為空？否則全部退還？

WHERE (@UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL ) 
OR (ISNULL(@UserRole, '') <> 'Analyst')

正如上面大衛斯皮萊特的回答NULL所解釋的那樣，問題很可能是與 的比較。當 時，比較將給出（並且該行不會通過測試）。@UserRole = 'Analyst'``SupervisorApprovedBy = NULL``UNKNOWN``WHERE

CASE您可以使用嵌套表達式重寫：

WHERE 1 =
 CASE 
   WHEN @UserRole = 'Analyst' THEN 
       CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
   WHEN SupervisorApprovedBy IS NOT NULL THEN 1
 END

或者一個複雜的CASE表達式：

WHERE 1 =
 CASE 
   WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
   WHEN @UserRole = 'Analyst' THEN 0
   WHEN SupervisorApprovedBy IS NOT NULL THEN 1
 END

或者更容易理解AND/ OR：

WHERE 
   @UserRole = 'Analyst'  AND SupervisorApprovedBy IS NULL 
OR @UserRole <> 'Analyst' AND SupervisorApprovedBy IS NOT NULL 
OR @UserRole IS NULL      AND SupervisorApprovedBy IS NOT NULL 

---

另一個問題是SupervisorApprovedBy = SupervisorApprovedBy（以及我上面使用的等價物SupervisorApprovedBy IS NOT NULL）不會給你“所有數據”。SupervisorApprovedBy不返回為 null的行。如果您確實想要它們，則應全部調整條件：

WHERE 1 =
 CASE 
   WHEN @UserRole = 'Analyst' THEN 
       CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
   ELSE 1
 END


WHERE 1 =
 CASE 
   WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
   WHEN @UserRole = 'Analyst' THEN 0
   ELSE 1
 END


WHERE 
   @UserRole = 'Analyst'  AND SupervisorApprovedBy IS NULL 
OR @UserRole <> 'Analyst' 
OR @UserRole IS NULL       

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