如何合并两个带GROUP BY计数的SQL查询 同时统计总文件与已关闭文件数

错误根源 #

你编写的合并查询存在两个核心问题：

• 语句中添加了WHERE hir.rapor_tipi = 3过滤条件，会直接排除所有没有关联「类型为3的结案报告」的hasar_ihbar记录，最终统计出的total_files仅包含已结案的文件，和第一个查询统计全量总文件的逻辑完全不符。
• 若hasar_ihbar和hasar_ihbar_rapor存在一对多关联，直接用count(hi.id)统计会出现重复计数的问题，结果虚高。

可用解决方案 #

方案1：子查询关联（逻辑清晰，易维护） #

分别保留你原来两个可用的查询作为子查询，再通过公共字段关联即可得到正确结果：

SELECT 
    t1.eksper_id,
    t1.ad,
    t1.total_files,
    COALESCE(t2.closed_files, 0) AS closed_files
FROM 
    -- 统计各主体总文件数
    (
        SELECT hi.eksper_id, ef.ad, COUNT(hi.eksper_id) AS total_files
        FROM hasar_ihbar AS hi 
        LEFT OUTER JOIN eksper_firma ef ON ef.id = hi.eksper_id
        GROUP BY hi.eksper_id, ef.ad
    ) t1
LEFT JOIN 
    -- 统计各主体已关闭文件数
    (
        SELECT ef.id AS eksper_id, ef.ad, COUNT(DISTINCT hi.id) AS closed_files
        FROM hasar_ihbar_rapor hir
        LEFT OUTER JOIN hasar_ihbar hi ON hi.id = hir.hasar_ihbar_id
        LEFT OUTER JOIN eksper_firma ef ON ef.id = hi.eksper_id
        WHERE hir.rapor_tipi = 3 
        GROUP BY ef.id, ef.ad
    ) t2 ON t1.eksper_id = t2.eksper_id

方案2：条件聚合（性能更优，单次查询出结果） #

从全量案件表出发左联报告表，通过条件计数直接统计两个指标：

SELECT 
    hi.eksper_id,
    ef.ad,
    COUNT(DISTINCT hi.id) AS total_files,
    COUNT(DISTINCT CASE WHEN hir.rapor_tipi = 3 THEN hi.id END) AS closed_files
FROM hasar_ihbar hi
LEFT JOIN eksper_firma ef ON ef.id = hi.eksper_id
LEFT JOIN hasar_ihbar_rapor hir ON hi.id = hir.hasar_ihbar_id
GROUP BY hi.eksper_id, ef.ad

逻辑说明：CASE WHEN对不符合「已关闭」条件的记录返回NULL，COUNT统计时会自动忽略NULL值，即可得到准确的已关闭文件数，全程没有过滤全量案件记录，总文件数统计也符合要求。

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