如何合并两条SQL查询，输出含CRIT、LOW、Branch的统计结果？

可以通过条件聚合的方式，将两个查询合并为一次查询，避免重复的表关联和过滤逻辑，同时直接输出包含CRIT、LOW、branch三列的结果：

SELECT
    A.branch,
    COUNT(CASE WHEN CAST(B.score AS float) > 8 THEN A.Name END) AS CRIT,
    COUNT(CASE WHEN CAST(B.score AS float) < 4 THEN A.Name END) AS LOW
FROM  
    TableA AS A
INNER JOIN 
    TableB AS B ON A.Name = B.Name -- 注意：原查询中的`V.Name`应为笔误，修正为`B.Name`
WHERE
    B.state <> 'DONE'
    AND DATEDIFF(day, B.last_found, GETDATE()) > 30
GROUP BY
    A.branch

逻辑说明： #

• 仅执行一次TableA与TableB的关联，以及基础条件过滤（state <> 'DONE'、last_found超过30天），比两次独立查询效率更高。
• 通过CASE WHEN在COUNT函数中实现条件统计：
  • 当score > 8时，统计符合条件的Name数量，对应CRIT列
  • 当score < 4时，统计符合条件的Name数量，对应LOW列
• 按branch分组后，直接得到每个分支对应的两类统计值

备选方案（保留原查询结构）： #

如果需要基于原有两个查询结果合并，可使用FULL JOIN确保不遗漏任何分支：

SELECT
    COALESCE(c.branch, l.branch) AS branch,
    ISNULL(c.CRIT, 0) AS CRIT,
    ISNULL(l.LOW, 0) AS LOW
FROM
    (
        -- 原第一条查询
        SELECT
             COUNT(Name) AS CRIT,
             branch
        FROM
            (SELECT
                 A.Name,
                 A.branch,
                 B.score,
                 B.last_found
             FROM  
                 TableA AS A
             INNER JOIN 
                 TableB AS B ON A.Name = B.Name
             WHERE
                 B.state <> 'DONE'
                 AND DATEDIFF(day, B.last_found, GETDATE()) > 30
                 AND CAST(B.score AS float) > 8) AS X
         GROUP BY
             branch
    ) c
FULL JOIN
    (
        -- 原第二条查询
        SELECT
             COUNT(Name) AS LOW,
             branch
        FROM
            (SELECT
                 A.Name,
                 A.branch,
                 B.score,
                 B.last_found
             FROM   
                 TableA AS A
             INNER JOIN 
                 TableB AS B ON A.Name = B.Name
             WHERE
                 B.state <> 'DONE'
                 AND DATEDIFF(day, B.last_found, GETDATE()) > 30
                 AND CAST(B.score AS float) < 4) AS X
         GROUP BY
             branch
    ) l ON c.branch = l.branch

该方案通过FULL JOIN合并两个子查询结果，用ISNULL处理某类统计值为0的场景，但性能不如条件聚合方案。

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