结合两个大型多 JOIN 和 GROUP BY 查询

小编典典

结合两个大型多 JOIN 和 GROUP BY 查询

sql

所以我有一个查询正在执行一个GROUP BY需要大量连接才能使其基本上执行行数聚合的查询

    SELECT ae.ServerName, Count(ccs.ApplicationID) [ComponentCount] 
    FROM [dbo].[APM_CurrentComponentStatus] ccs
    JOIN [APM_Applications] ap ON ccs.ApplicationID = ap.ID
    JOIN [Nodes] node ON ap.NodeID = node.NodeID
    JOIN [Engines] e ON node.EngineID = e.EngineID
    JOIN [AllEngines] ae ON ae.EngineID = e.EngineID
    --WHERE ap.Unmanaged = 'False'
    GROUP BY ae.ServerName
    ORDER BY [Component Count] DESC

然后有一个类似结构的查询，但对行数执行不同的聚合。

    SELECT ae.servername, count(i.interfaceID) [InterfaceCount]
    FROM interfaces i 
    JOIN [Nodes] node ON i.NodeID = node.NodeID 
    JOIN [Engines] e ON node.EngineID = e.EngineID 
    JOIN [AllEngines] ae ON ae.EngineID = e.EngineID 
    group by ae.servername

您如何将第二个查询嵌套到第一个查询中，以便最终列类似于 ServerName, ComponentCount, InterfaceCount ？

似乎我在这里遗漏了一些简单的东西，但也许不是，也许这超出了我的能力范围。

阅读 94

2022-07-20

共1个答案

小编典典

我不推荐这种类型的查询，最好优化一下。无论如何，制作最终结果的方法之一如下

Select 
  q1.ServerName, 
  q1.ComponentCount, 
  q2.InterfaceCount 
From 
  (
    SELECT 
      ae.ServerName, 
      Count(ccs.ApplicationID) [ComponentCount] 
    FROM 
      [dbo].[APM_CurrentComponentStatus] ccs 
      JOIN [APM_Applications] ap ON ccs.ApplicationID = ap.ID 
      JOIN [Nodes] node ON ap.NodeID = node.NodeID 
      JOIN [Engines] e ON node.EngineID = e.EngineID 
      JOIN [AllEngines] ae ON ae.EngineID = e.EngineID --WHERE ap.Unmanaged = 'False'
    GROUP BY 
      ae.ServerName 
    ORDER BY 
      [Component Count] DESC
  ) q1 
  Inner Join (
    SELECT 
      ae.servername, 
      count(i.interfaceID) [InterfaceCount] 
    FROM 
      interfaces i 
      JOIN [Nodes] node ON i.NodeID = node.NodeID 
      JOIN [Engines] e ON node.EngineID = e.EngineID 
      JOIN [AllEngines] ae ON ae.EngineID = e.EngineID 
    group by 
      ae.servername
  ) q2 on q1.ServerName = q2ServerName

2022-07-20