主页 / dba / 问题 / 8383
Accepted
AaronJAnderson
Asked: 2011-11-25 11:28:22 +0800 CST

使用仅包含特定单词的字段构建视图

  • 772

尝试构建一个简单的 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')
view

1 个回答

  1. Best Answer

    你拥有的是最简单和最直接的。

    • 您的 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'

    注意：您不需要与列名相同的别名...

    • 2

相关问题