如何优化SQL查询将关联多子表的结果合并为单行？

Hey there! I see the issue with your current query—right now, each device associated with a Setup is showing up in its own row because you're joining all three device tables to a single LinksAre join. Let's break down why that happens and fix it.

Why Your Current Query Scatters Data #

Your query uses one LinksAre LS join to connect the Setup to all three device types. Since each device (Controller/Platform/KVM) is a separate entry in LinksAre, this creates a row for each device match. So a single Setup with all three devices will result in 3 rows, each with one device's data and nulls for the others.

Solution 1: Join Each Device Type Separately #

The cleanest fix is to create separate LinksAre joins for each device type. This way, we pull all three device sets independently and merge them into one row per Setup:

select 
    R.Id as Rack_Id, 
    S.Id as Setup_Id,
    -- Controller details
    C.Id as Controller_Id,
    C.[Controller IP],
    -- Platform details
    P.Id as Platform_Id,
    P.ISMP,
    P.Platform,
    -- KVM details
    K.Id as KVM_Id,
    -- Rack and Setup metadata
    R.Capacity,
    R.[Rack location],
    S.[Location in Rack]
From #Racks R 
join LinksAre LR on LR.SourceId = R.Id 
join #Setups S on LR.TargetId = S.Id 

-- Join for Controller devices
left join LinksAre LS_Controller on LS_Controller.SourceId = S.Id 
left join #Controllers C on LS_Controller.TargetId = C.Id 

-- Join for Platform devices
left join LinksAre LS_Platform on LS_Platform.SourceId = S.Id 
left join #Platforms P on LS_Platform.TargetId = P.Id 

-- Join for KVM devices
left join LinksAre LS_KVM on LS_KVM.SourceId = S.Id 
left join #KVMs K on LS_KVM.TargetId = K.Id 

order by R.Id, S.Id

Solution 2: Use Subqueries for Each Device (Alternative) #

If you prefer a more compact approach, you can use correlated subqueries directly in the SELECT clause to pull each device's data. This works best if each Setup has at most one of each device type:

select 
    R.Id as Rack_Id, 
    S.Id as Setup_Id,
    -- Get Controller info
    (SELECT TOP 1 C.Id FROM #Controllers C 
     JOIN LinksAre LS ON LS.TargetId = C.Id 
     WHERE LS.SourceId = S.Id) as Controller_Id,
    (SELECT TOP 1 C.[Controller IP] FROM #Controllers C 
     JOIN LinksAre LS ON LS.TargetId = C.Id 
     WHERE LS.SourceId = S.Id) as [Controller IP],
    -- Get Platform info
    (SELECT TOP 1 P.Id FROM #Platforms P 
     JOIN LinksAre LS ON LS.TargetId = P.Id 
     WHERE LS.SourceId = S.Id) as Platform_Id,
    (SELECT TOP 1 P.ISMP FROM #Platforms P 
     JOIN LinksAre LS ON LS.TargetId = P.Id 
     WHERE LS.SourceId = S.Id) as ISMP,
    (SELECT TOP 1 P.Platform FROM #Platforms P 
     JOIN LinksAre LS ON LS.TargetId = P.Id 
     WHERE LS.SourceId = S.Id) as Platform,
    -- Get KVM info
    (SELECT TOP 1 K.Id FROM #KVMs K 
     JOIN LinksAre LS ON LS.TargetId = K.Id 
     WHERE LS.SourceId = S.Id) as KVM_Id,
    -- Rack and Setup metadata
    R.Capacity,
    R.[Rack location],
    S.[Location in Rack]
From #Racks R 
join LinksAre LR on LR.SourceId = R.Id 
join #Setups S on LR.TargetId = S.Id 
order by R.Id, S.Id

Handling Multiple Devices per Setup #

If a Setup can have multiple instances of a device type (e.g., two Controllers), use STRING_AGG (for SQL Server 2017+) to combine all related device data into a single field. Here's how to adjust Solution 1 for that case:

select 
    R.Id as Rack_Id, 
    S.Id as Setup_Id,
    STRING_AGG(C.Id, ', ') as Controller_Ids,
    STRING_AGG(C.[Controller IP], ', ') as Controller_IPs,
    STRING_AGG(P.Id, ', ') as Platform_Ids,
    STRING_AGG(P.ISMP, ', ') as Platform_ISMPs,
    STRING_AGG(P.Platform, ', ') as Platform_Names,
    STRING_AGG(K.Id, ', ') as KVM_Ids,
    R.Capacity,
    R.[Rack location],
    S.[Location in Rack]
From #Racks R 
join LinksAre LR on LR.SourceId = R.Id 
join #Setups S on LR.TargetId = S.Id 
left join LinksAre LS_Controller on LS_Controller.SourceId = S.Id 
left join #Controllers C on LS_Controller.TargetId = C.Id 
left join LinksAre LS_Platform on LS_Platform.SourceId = S.Id 
left join #Platforms P on LS_Platform.TargetId = P.Id 
left join LinksAre LS_KVM on LS_KVM.SourceId = S.Id 
left join #KVMs K on LS_KVM.TargetId = K.Id 
group by R.Id, S.Id, R.Capacity, R.[Rack location], S.[Location in Rack]
order by R.Id, S.Id

This will merge multiple device IDs/values into comma-separated strings for each device type.

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