时间戳

首页 > 解决方案 > 显示没有值的单位

sql - 显示没有值的单位

问题描述

我正在尝试从数据库中的计算机获取所有这些信息，但某些单元没有保修信息并且未被选中。如何让没有保修信息的设备与其他设备一起显示？

SELECT Distinct A.[UNITID]
  ,A.[NAME]
  ,A.[DESCRIPTION]
  ,A.[UnitModel]
  ,Models.Model
  ,A.[FormFactor]
  ,A.[SerialNumber]
  ,A.[Manufacturer]
  ,A.[MACAddress]
  ,A.[IPAddress]
  ,I.[VALUE] as Memory
  ,Inv.[VALUE] as HDD_C
  ,INVType.VALUE As OSType
  ,C.[VALUE] as BiosDate
  ,WarrentyEnd.VALUE as WarrentyEnd
  ,WarrentyStart.VALUE as WarrentyStart
  ,WarrentyDescription.VALUE as WarrentyDescription
  FROM [DB].[vCAPApc] A, [DB].[Models] Models, [DB].[UNIT] Unit, [DB].[INV] I, [DB].[INV] Inv, [DB].[INV] INVType, [DB].[INV] Formfactor, [DB].[CSI] C, [DB].[CSI] WarrentyEnd, [DB].[CSI] WarrentyStart, [DB].[CSI] WarrentyDescription
  WHERE
  Models.ID = A.UnitModel
  AND Unit.UNITID = A.UNITID
  AND Inv.UNITID = A.UNITID
  AND I.UNITID = A.UNITID
  AND I.NAME Like 'Total M%'
  AND Inv.NAME = 'Drive C: Size'
  AND C.UNITID = A.UNITID
  AND C.NAME = 'Bios Date'
  AND INVType.NAME = 'Type'
  AND INVType.SECTION = 'Operating System'
  AND INVType.VALUE != 'Member Server'
  AND A.[FormFactor] != 'Virtual Machine'
  AND A.NAME != 'VAGTEN-2'
  AND WarrentyEnd.UNITID = A.UNITID
  AND WarrentyEnd.SECTION = 'Garanti'
  AND WarrentyEnd.NAME = 'Ophører'
  AND WarrentyStart.UNITID = A.UNITID
  AND WarrentyStart.SECTION = 'Garanti'
  AND WarrentyStart.NAME = 'Start'
  AND WarrentyDescription.UNITID = A.UNITID
  AND WarrentyDescription.SECTION = 'Garanti'
  AND WarrentyDescription.NAME = 'Beskrivelse'

标签： sql

解决方案

对于为空的字段，请尝试合并以使它们满足您的条件。像这样更改 where 查询，

AND COALESCE(WarrentyEnd.UNITID,A.UNITID) = A.UNITID
AND COALESCE(WarrentyEnd.SECTION,'Garanti') = 'Garanti'
AND COALESCE(WarrentyEnd.NAME,'Ophører') = 'Ophører'

所有保修字段都应遵循此类型的空检查以包含在结果集中。

推荐阅读