如何在计算机信息SQL查询中包含无保修信息的设备？

这问题我熟！你当前的SQL语句用了隐式内连接（通过逗号分隔表并在WHERE里关联），对于没有保修记录的设备，因为WarrentyEnd、WarrentyStart、WarrentyDescription这三个CSI表中没有对应记录，内连接会直接把这些设备排除在结果之外。要让无保修信息的设备也显示出来，核心是把保修相关表的连接改成LEFT JOIN（左外连接），同时调整条件位置避免左连接失效。

关键修改点 #

• 将所有隐式内连接改为显式INNER JOIN，让逻辑更清晰易读
• 把保修相关的三个CSI表连接改为LEFT JOIN，确保主表（vCAPApc）的设备即使无保修记录也能被保留
• 把原WHERE子句中针对保修表的SECTION和NAME筛选条件，移到对应LEFT JOIN的ON子句中（如果放在WHERE里，会把左连接返回的NULL记录再次过滤，等同于内连接）

修改后的完整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
INNER JOIN [DB].[Models] Models ON Models.ID = A.UnitModel
INNER JOIN [DB].[UNIT] Unit ON Unit.UNITID = A.UNITID
INNER JOIN [DB].[INV] I ON I.UNITID = A.UNITID AND I.NAME Like 'Total M%'
INNER JOIN [DB].[INV] Inv ON Inv.UNITID = A.UNITID AND Inv.NAME = 'Drive C: Size'
INNER JOIN [DB].[INV] INVType ON INVType.UNITID = A.UNITID 
    AND INVType.NAME = 'Type' 
    AND INVType.SECTION = 'Operating System' 
    AND INVType.VALUE != 'Member Server'
INNER JOIN [DB].[CSI] C ON C.UNITID = A.UNITID AND C.NAME = 'Bios Date'
-- 以下三个改为LEFT JOIN，并将筛选条件放到ON子句
LEFT JOIN [DB].[CSI] WarrentyEnd ON WarrentyEnd.UNITID = A.UNITID 
    AND WarrentyEnd.SECTION = 'Garanti' 
    AND WarrentyEnd.NAME = 'Ophører'
LEFT JOIN [DB].[CSI] WarrentyStart ON WarrentyStart.UNITID = A.UNITID 
    AND WarrentyStart.SECTION = 'Garanti' 
    AND WarrentyStart.NAME = 'Start'
LEFT JOIN [DB].[CSI] WarrentyDescription ON WarrentyDescription.UNITID = A.UNITID 
    AND WarrentyDescription.SECTION = 'Garanti' 
    AND WarrentyDescription.NAME = 'Beskrivelse'
WHERE 
    A.[FormFactor] != 'Virtual Machine' 
    AND A.NAME != 'VAGTEN-2'

效果说明 #

修改后，没有保修信息的设备会正常显示在结果中，对应的WarrentyEnd、WarrentyStart、WarrentyDescription字段值会显示为NULL，其他设备信息不受影响。

内容的提问来源于stack exchange，提问作者TBoy3