如何合并两个SQL查询 实现计算机多Instance实例逗号分隔展示

解决后的合并查询 #

USE DBname
SELECT 
    tblComputer.HostName, 
    tblComputer.Manufacturer, 
    tblComputer.Model, 
    tblComputerHardware.ProcessorType, 
    tblComputerHardware.ProcessorCount, 
    tblComputerHardware.CoreCount,
    -- 按当前计算机ID拼接所有实例
    STUFF((
        SELECT ',' + InstanceName
        FROM tblInstances t2
        WHERE t2.ComputerID = tblComputer.ComputerID -- 关键：关联外层当前计算机ID
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS AllInstanceNames
FROM tblDatabases 
JOIN tblComputer ON tblDatabases.ComputerID = tblComputer.ComputerID 
JOIN tblComputerHardware ON tblComputer.ComputerID = tblComputerHardware.ComputerID 
WHERE IsVirtual = 0
-- 按非聚合字段分组，实现单台计算机仅返回一行
GROUP BY 
    tblComputer.HostName, 
    tblComputer.Manufacturer, 
    tblComputer.Model, 
    tblComputerHardware.ProcessorType, 
    tblComputerHardware.ProcessorCount, 
    tblComputerHardware.CoreCount,
    tblComputer.ComputerID

核心修改说明 #

• 给拼接实例的子查询增加了WHERE t2.ComputerID = tblComputer.ComputerID过滤条件，仅拼接当前计算机对应的实例，避免全局所有实例混在一起
• 去掉了原查询中对tblInstances的直接JOIN，避免单台计算机有多行实例时重复返回多条相同计算机记录
• 新增TYPE和.value('.', 'NVARCHAR(MAX)')处理，避免实例名包含特殊字符时出现XML转义问题
• 对所有非聚合的查询字段做GROUP BY，保证每台计算机仅返回一行结果

高版本SQL Server简化方案 #

如果使用SQL Server 2017及以上版本，可以直接用内置的STRING_AGG函数实现，写法更简洁：

USE DBname
SELECT 
    tblComputer.HostName, 
    tblComputer.Manufacturer, 
    tblComputer.Model, 
    tblComputerHardware.ProcessorType, 
    tblComputerHardware.ProcessorCount, 
    tblComputerHardware.CoreCount,
    STRING_AGG(tblInstances.InstanceName, ',') AS AllInstanceNames
FROM tblDatabases 
JOIN tblComputer ON tblDatabases.ComputerID = tblComputer.ComputerID 
JOIN tblComputerHardware ON tblComputer.ComputerID = tblComputerHardware.ComputerID 
JOIN tblInstances ON tblComputer.ComputerID = tblInstances.ComputerID 
WHERE IsVirtual = 0
GROUP BY 
    tblComputer.HostName, 
    tblComputer.Manufacturer, 
    tblComputer.Model, 
    tblComputerHardware.ProcessorType, 
    tblComputerHardware.ProcessorCount, 
    tblComputerHardware.CoreCount,
    tblComputer.ComputerID

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