如何将针对table1与table2的两个SQL统计查询合并为一个？

这里提供两种可行的合并方案，你可以根据实际数据库环境选择：

方案一：子查询关联 #

通过子查询分别统计两个表的结果，再与codesTable关联，确保所有code_id都能展示，无对应记录时显示0：

SELECT 
    ct.code_id,
    COALESCE(t1.count_num, 0) AS table1_count,
    COALESCE(t2.count_num, 0) AS table2_count
FROM codesTable ct
LEFT JOIN (
    SELECT 
        ct_inner.code_id,
        COUNT(*) AS count_num
    FROM table1 t1
    JOIN codesTable ct_inner ON t1.codeid = ct_inner.code_id
    WHERE t1.type_no IN (1,2,3,4,5,6,7,8,9,10) 
      AND t1.recoverd_value IN (0,1) 
    GROUP BY ct_inner.code_id
) t1 ON ct.code_id = t1.code_id
LEFT JOIN (
    SELECT 
        ct_inner.code_id,
        COUNT(*) AS count_num
    FROM table2 t2
    JOIN codesTable ct_inner ON t2.codeid = ct_inner.code_id
    WHERE t2.type_no IN (1,2,3,4,5,6,7,8,9,10) 
      AND t2.recoverd_value IN (0,1) 
    GROUP BY ct_inner.code_id
) t2 ON ct.code_id = t2.code_id;

• COALESCE函数用于将空值转换为0，避免无匹配记录时结果显示NULL。
• 左连接codesTable能保证所有存在的code_id都出现在结果中，即使某个表没有对应数据。

方案二：UNION ALL合并后聚合 #

先合并两个表的符合条件数据并标记来源，再按code_id分组统计：

SELECT 
    ct.code_id,
    COUNT(CASE WHEN source_table = 'table1' THEN 1 END) AS table1_count,
    COUNT(CASE WHEN source_table = 'table2' THEN 1 END) AS table2_count
FROM codesTable ct
LEFT JOIN (
    SELECT 
        ct_inner.code_id,
        'table1' AS source_table
    FROM table1 t1
    JOIN codesTable ct_inner ON t1.codeid = ct_inner.code_id
    WHERE t1.type_no IN (1,2,3,4,5,6,7,8,9,10) 
      AND t1.recoverd_value IN (0,1)
    UNION ALL
    SELECT 
        ct_inner.code_id,
        'table2' AS source_table
    FROM table2 t2
    JOIN codesTable ct_inner ON t2.codeid = ct_inner.code_id
    WHERE t2.type_no IN (1,2,3,4,5,6,7,8,9,10) 
      AND t2.recoverd_value IN (0,1)
) combined ON ct.code_id = combined.code_id
GROUP BY ct.code_id;

• UNION ALL保留两个表的所有符合条件数据，并通过source_table字段标记来源。
• 条件COUNT函数会分别统计来自两个表的记录数，逻辑更直观，适合数据量不大的场景。

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