如何将两个含多JOIN与GROUP BY的SQL查询合并，输出指定三列

你可以通过子查询关联或者**CTE（公共表表达式）**的方式，将两个统计查询的结果合并，同时输出三列数据。以下是两种具体实现：

方法一：子查询关联 #

将第二个统计接口数的查询作为子查询，通过ServerName与第一个统计组件数的查询结果关联：

SELECT 
    main.ServerName,
    main.ComponentCount,
    ISNULL(sub.InterfaceCount, 0) AS InterfaceCount
FROM (
    -- 组件数统计查询
    SELECT ae.ServerName, Count(ccs.ApplicationID) AS 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
) main
LEFT JOIN (
    -- 接口数统计查询
    SELECT ae.servername, count(i.interfaceID) AS 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
) sub ON main.ServerName = sub.ServerName
ORDER BY main.ComponentCount DESC

• 使用LEFT JOIN可以保留所有有组件数据的Server，即使该Server没有接口数据，接口数会显示为0（通过ISNULL处理）；如果只需要同时存在两种数据的Server，替换为INNER JOIN即可。

方法二：CTE（公共表表达式） #

用CTE分别定义两个统计结果，再进行关联查询，可读性更强：

WITH ComponentStats AS (
    SELECT ae.ServerName, Count(ccs.ApplicationID) AS 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
),
InterfaceStats AS (
    SELECT ae.servername, count(i.interfaceID) AS 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
)
SELECT 
    cs.ServerName,
    cs.ComponentCount,
    ISNULL(is.InterfaceCount, 0) AS InterfaceCount
FROM ComponentStats cs
LEFT JOIN InterfaceStats is ON cs.ServerName = is.ServerName
ORDER BY cs.ComponentCount DESC

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