根据字段值排除项目

Question

我使用下面的脚本创建了一个视图，但其中一个字段正在提取我不需要的信息。在"IsServer"列中，属于服务器的项用1标记，而不是服务器的项则用0标记。有没有一种方法可以创建视图并排除IsServer列中标记为1的服务器项？我不确定如何将其构建到创建视图脚本中。

CREATE VIEW [dbo].[vw_Cherwell_Machines] AS 
SELECT dbo.rptComputers.HostName, 
dbo.rptComputers.Vendor, 
dbo.tblComputer.IsVirtual, 
dbo.rptComputers.ProcessorCount, 
dbo.tblComputer.IsServer, 
dbo.rptComputers.BiosSerialNumber, 
dbo.rptComputers.PhysicalMemory, 
dbo.rptComputers.ProcessorType, 
dbo.rptComputers.ProcessorSpeed, 
dbo.rptComputers.MostFrequentUser, 
dbo.tblComputer.Domain, 
dbo.rptComputers.ClientVersion, 
dbo.rptComputers.ClientInstallDate, 
dbo.rptComputers.ClientConfigurationName, 
dbo.rptComputers.BiosVersion, 
dbo.rptComputers.BiosDate, 
dbo.rptComputers.Manufacturer, 
dbo.rptComputers.Model, 
dbo.rptComputers.IsPortable, 
dbo.rptComputers.OperatingSystem, 
dbo.tblComputer.OSServicePack,
dbo.rptComputers.ComputerStatusCode, 
dbo.rptComputers.IPAddress AS 'MultipleIPAddress', 

CASE WHEN PATINDEX ('%[, ]%',dbo.rptComputers.IPAddress) > 0 THEN 
left(dbo.rptComputers.IPAddress,(PATINDEX('%[, ]%',dbo.rptComputers.IPAddress))-1) 
ELSE dbo.rptComputers.IPAddress 
END  AS 'IPAddress',

dbo.rptComputers.LastScanDate
FROM dbo.rptComputers INNER JOIN dbo.tblComputer ON dbo.rptComputers.CID = dbo.tblComputer.CID 
AND dbo.rptComputers.ComputerID = dbo.tblComputer.ComputerID
GROUP BY dbo.rptComputers.HostName, 
dbo.tblComputer.IsVirtual, 
dbo.rptComputers.Vendor, 
dbo.rptComputers.ProcessorCount, 
dbo.rptComputers.BiosSerialNumber, 
dbo.tblComputer.IsServer, 
dbo.rptComputers.PhysicalMemory,
dbo.rptComputers.ProcessorType, 
dbo.rptComputers.ProcessorSpeed, 
dbo.rptComputers.MostFrequentUser, 
dbo.tblComputer.Domain,
dbo.rptComputers.ClientVersion, 
dbo.rptComputers.ClientInstallDate, 
dbo.rptComputers.ClientConfigurationName, 
dbo.rptComputers.BiosVersion, 
dbo.rptComputers.BiosDate, 
dbo.rptComputers.Manufacturer, 
dbo.rptComputers.Model, 
dbo.rptComputers.IsPortable, 
dbo.rptComputers.OperatingSystem, 
dbo.tblComputer.OSServicePack,
dbo.rptComputers.ComputerStatusCode, 
dbo.rptComputers.IPAddress, 
dbo.rptComputers.LastScanDate

Answer 1

您需要一个经过过滤的视图；即具有WHERE子句的视图。类似这样的东西(我在评论中标记了我添加到您代码的开始/结束(：

CREATE VIEW [dbo].[vw_Cherwell_Machines] AS 
SELECT dbo.rptComputers.HostName, 
dbo.rptComputers.Vendor, 
dbo.tblComputer.IsVirtual, 
dbo.rptComputers.ProcessorCount, 
dbo.tblComputer.IsServer, 
dbo.rptComputers.BiosSerialNumber, 
dbo.rptComputers.PhysicalMemory, 
dbo.rptComputers.ProcessorType, 
dbo.rptComputers.ProcessorSpeed, 
dbo.rptComputers.MostFrequentUser, 
dbo.tblComputer.Domain, 
dbo.rptComputers.ClientVersion, 
dbo.rptComputers.ClientInstallDate, 
dbo.rptComputers.ClientConfigurationName, 
dbo.rptComputers.BiosVersion, 
dbo.rptComputers.BiosDate, 
dbo.rptComputers.Manufacturer, 
dbo.rptComputers.Model, 
dbo.rptComputers.IsPortable, 
dbo.rptComputers.OperatingSystem, 
dbo.tblComputer.OSServicePack,
dbo.rptComputers.ComputerStatusCode, 
dbo.rptComputers.IPAddress AS 'MultipleIPAddress', 

CASE WHEN PATINDEX ('%[, ]%',dbo.rptComputers.IPAddress) > 0 THEN 
left(dbo.rptComputers.IPAddress,(PATINDEX('%[, ]%',dbo.rptComputers.IPAddress))-1) 
ELSE dbo.rptComputers.IPAddress 
END  AS 'IPAddress',

dbo.rptComputers.LastScanDate
FROM dbo.rptComputers INNER JOIN dbo.tblComputer ON dbo.rptComputers.CID = dbo.tblComputer.CID 
AND dbo.rptComputers.ComputerID = dbo.tblComputer.ComputerID
/*begin change*/
WHERE tblComputer.IsServer = 0 
/*end change*/
GROUP BY dbo.rptComputers.HostName, 
dbo.tblComputer.IsVirtual, 
dbo.rptComputers.Vendor, 
dbo.rptComputers.ProcessorCount, 
dbo.rptComputers.BiosSerialNumber, 
dbo.tblComputer.IsServer, 
dbo.rptComputers.PhysicalMemory,
dbo.rptComputers.ProcessorType, 
dbo.rptComputers.ProcessorSpeed, 
dbo.rptComputers.MostFrequentUser, 
dbo.tblComputer.Domain,
dbo.rptComputers.ClientVersion, 
dbo.rptComputers.ClientInstallDate, 
dbo.rptComputers.ClientConfigurationName, 
dbo.rptComputers.BiosVersion, 
dbo.rptComputers.BiosDate, 
dbo.rptComputers.Manufacturer, 
dbo.rptComputers.Model, 
dbo.rptComputers.IsPortable, 
dbo.rptComputers.OperatingSystem, 
dbo.tblComputer.OSServicePack,
dbo.rptComputers.ComputerStatusCode, 
dbo.rptComputers.IPAddress, 
dbo.rptComputers.LastScanDate

文档：https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes?view=sql-server-ver15一定要看到；"限制"；部分，以防您的环境中有什么东西在这里不起作用。