使用僅包含某個單詞的欄位建構視圖

嘗試建構一個簡單的 SQL 查詢…我有一個表，裡面填滿了“是”和“否”

我想要一個只顯示包含“否”的記錄的視圖

SELECT PhysicalBuild.Name AS Name,
 PhysicalBuild.Labels AS Labels,
 PhysicalBuild.DNSDRAC AS DNSDRAC,
 PhysicalBuild.DNSHOST AS DNSHOST,
 PhysicalBuild.PRTGDRAC AS PRTGDRAC,
 PhysicalBuild.PRTGHOST AS PRTGHOST,
 PhysicalBuild.LouKVM AS LouKVM,
 PhysicalBuild.SwitchPorts AS SwitchPorts,
 PhysicalBuild.FiberSwitch AS FiberSwitch
FROM PhysicalBuild 

但我希望它是 WHERE * = “No” 或類似的東西……我的語法不正確。任何人？

編輯：我想出了這個。有沒有更清潔/更簡單的方法來處理這個問題？如何將包含“是”的記錄更改為 NULL 以使其不顯示？所以只顯示否？

SELECT PhysicalBuild.Name AS Name,
 PhysicalBuild.Labels AS Labels,
 PhysicalBuild.DNSDRAC AS DNSDRAC,
 PhysicalBuild.DNSHOST AS DNSHOST,
 PhysicalBuild.PRTGDRAC AS PRTGDRAC,
 PhysicalBuild.PRTGHOST AS PRTGHOST,
 PhysicalBuild.LouKVM AS LouKVM,
 PhysicalBuild.SwitchPorts AS SwitchPorts,
 PhysicalBuild.FiberSwitch AS FiberSwitch
FROM PhysicalBuild
WHERE (PhysicalBuild.Labels = 'No') OR
 (PhysicalBuild.DNSDRAC = 'No') OR                     
 (PhysicalBuild.DNSHOST = 'No') OR
 (PhysicalBuild.PRTGDRAC = 'No') OR
 (PhysicalBuild.PRTGHOST = 'No') OR
 (PhysicalBuild.LouKVM = 'No') OR
 (PhysicalBuild.SwitchPorts = 'No') OR
 (PhysicalBuild.FiberSwitch = 'No')

你所擁有的是最簡單和最直接的。

• 您的 WHERE 將需要某處提到的每一列
• 將 YES 更改為 NULL 需要在每一列上都有一個函式

對於某些 RDBMS，您可以在表級別創建計算列。

ALTER TABLE PhysicalBuild
  ADD SummaryState AS 
    CASE WHEN Labels = 'No' AND 
       DNSDRAC = 'No' AND                
       DNSHOST = 'No' AND 
       PRTGDRAC = 'No' AND 
       PRTGHOST = 'No' AND 
       LouKVM = 'No' AND 
       SwitchPorts = 'No' AND 
       FiberSwitch = 'No'
    THEN 'No' ELSE 'Yes' END

那麼你的查詢是

...
FROM PhysicalBuild
WHERE SummaryState = 'No'

注意：您不需要與列名相同的別名…

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