咨询优化两表关联统计多类型服务器数量的SQL写法

现有代码可正常运行，通过关联两张表统计不同类型服务器的使用数、闲置数和总数，以下是更清晰高效的写法，适合用于创建存储过程：

原查询代码 #

SELECT * FROM

    (SELECT 
        Count(*) AS ApiServerUsed, 
        (MaxLimit - Count(*)) AS ApiServerUnused, 
        MaxLimit as ApiServerTotal
    FROM [Client].[ClientSettings] LEFT JOIN [Setting].[Server] ON DockerApiHost = IpAddress
    WHERE Type = 1
    GROUP BY MaxLimit) s1,

    (SELECT 
        Count(*) AS GuiServerUsed, 
        (MaxLimit - Count(*)) AS GuiServerUnused, 
        MaxLimit as GuiServerTotal
    FROM [Client].[ClientSettings] LEFT JOIN [Setting].[Server] ON DockerGuiHost = IpAddress
    WHERE Type = 2
    GROUP BY MaxLimit) s2,
    
    (SELECT 
        Count(*) AS DbServerUsed, 
        (MaxLimit - Count(*)) AS DbServerUnused, 
        MaxLimit as DbServerTotal
    FROM [Client].[ClientSettings] LEFT JOIN [Setting].[Server] ON DbHost = IpAddress
    WHERE Type = 3
    GROUP BY MaxLimit) s3,

    (SELECT 
        Count(*) AS CacheServerUsed,
        (MaxLimit - Count(*)) AS CacheServerUnused,
        MaxLimit as CacheServerTotal
    FROM [Client].[ClientSettings] LEFT JOIN [Setting].[Server] ON CacheHost = IpAddress
    WHERE Type = 4
    GROUP BY MaxLimit) s4,

    (SELECT 
        Count(*) AS MqServerUsed,
        (MaxLimit - Count(*)) AS MqServerUnused,
        MaxLimit as MqServerTotal
    FROM [Client].[ClientSettings] LEFT JOIN [Setting].[Server] ON RabbitMQHost = IpAddress
    WHERE Type = 5
    GROUP BY MaxLimit) s5

优化后的查询（条件聚合写法） #

SELECT
    -- API服务器统计
    COUNT(CASE WHEN cs.Type = 1 THEN s.IpAddress END) AS ApiServerUsed,
    MAX(CASE WHEN cs.Type = 1 THEN cs.MaxLimit END) - COUNT(CASE WHEN cs.Type = 1 THEN s.IpAddress END) AS ApiServerUnused,
    MAX(CASE WHEN cs.Type = 1 THEN cs.MaxLimit END) AS ApiServerTotal,
    -- GUI服务器统计
    COUNT(CASE WHEN cs.Type = 2 THEN s.IpAddress END) AS GuiServerUsed,
    MAX(CASE WHEN cs.Type = 2 THEN cs.MaxLimit END) - COUNT(CASE WHEN cs.Type = 2 THEN s.IpAddress END) AS GuiServerUnused,
    MAX(CASE WHEN cs.Type = 2 THEN cs.MaxLimit END) AS GuiServerTotal,
    -- DB服务器统计
    COUNT(CASE WHEN cs.Type = 3 THEN s.IpAddress END) AS DbServerUsed,
    MAX(CASE WHEN cs.Type = 3 THEN cs.MaxLimit END) - COUNT(CASE WHEN cs.Type = 3 THEN s.IpAddress END) AS DbServerUnused,
    MAX(CASE WHEN cs.Type = 3 THEN cs.MaxLimit END) AS DbServerTotal,
    -- 缓存服务器统计
    COUNT(CASE WHEN cs.Type = 4 THEN s.IpAddress END) AS CacheServerUsed,
    MAX(CASE WHEN cs.Type = 4 THEN cs.MaxLimit END) - COUNT(CASE WHEN cs.Type = 4 THEN s.IpAddress END) AS CacheServerUnused,
    MAX(CASE WHEN cs.Type = 4 THEN cs.MaxLimit END) AS CacheServerTotal,
    -- MQ服务器统计
    COUNT(CASE WHEN cs.Type = 5 THEN s.IpAddress END) AS MqServerUsed,
    MAX(CASE WHEN cs.Type = 5 THEN cs.MaxLimit END) - COUNT(CASE WHEN cs.Type = 5 THEN s.IpAddress END) AS MqServerUnused,
    MAX(CASE WHEN cs.Type = 5 THEN cs.MaxLimit END) AS MqServerTotal
FROM [Client].[ClientSettings] cs
LEFT JOIN [Setting].[Server] s ON 
    (cs.Type = 1 AND cs.DockerApiHost = s.IpAddress)
    OR (cs.Type = 2 AND cs.DockerGuiHost = s.IpAddress)
    OR (cs.Type = 3 AND cs.DbHost = s.IpAddress)
    OR (cs.Type = 4 AND cs.CacheHost = s.IpAddress)
    OR (cs.Type = 5 AND cs.RabbitMQHost = s.IpAddress)
WHERE cs.Type IN (1,2,3,4,5)
-- 若同一Type下的MaxLimit值唯一，可去掉GROUP BY；否则保留GROUP BY cs.Type确保聚合正确
GROUP BY cs.Type

优化说明 #

• 减少重复操作：仅执行一次表关联，避免原写法中多次重复JOIN和分组，降低数据库IO开销
• 结构紧凑易维护：所有统计逻辑集中在一个查询块内，新增服务器类型时只需添加对应CASE分支，无需新增子查询
• 避免冗余结果：原写法中多个子查询会产生笛卡尔积（若子查询返回多行），优化后返回单条汇总结果，更符合统计需求
• 逻辑清晰直观：通过CASE WHEN区分不同类型服务器的统计规则，可读性更强

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