MySQL关联表查询count返回值不正确 如何实现两表统计结果二选一

问题根源 #

• 当前语句使用INNER JOIN同时关联supreme_applicable_areas和appeal_applicable_areas，要求两张关联表同时存在匹配数据才会返回结果，遗漏了仅单表匹配的区域数据
• 关联cp_cases_counsel时使用OR条件会产生不必要的笛卡尔积，导致统计计数出现偏差，出现两张表同时返回统计值的异常结果
• 你要求两个表的统计结果仅取其一，可以按业务优先级优先取最高法院数据，无匹配时再取上诉法院数据，避免两类统计值同时出现

优化方案 #

推荐使用UNION ALL拆分两类数据分别统计，避免交叉关联导致的计数错误，示例语句如下：

SELECT 
    area,
    id,
    SUM(is_supreme) AS supreme_court_cases,
    SUM(is_appeal) AS appeal_court_cases
FROM (
    -- 统计最高法院匹配数据
    SELECT DISTINCT
        a.area,
        a.id,
        1 AS is_supreme,
        0 AS is_appeal
    FROM applicable_areas a
    JOIN supreme_applicable_areas s ON s.applicable_area_id = a.id
    JOIN cp_cases_counsel c ON c.suitno = s.suitno
    WHERE c.counsel_id = 54

    UNION ALL

    -- 统计上诉法院匹配数据，排除已在最高法院统计过的区域
    SELECT DISTINCT
        a.area,
        a.id,
        0 AS is_supreme,
        1 AS is_appeal
    FROM applicable_areas a
    JOIN appeal_applicable_areas ap ON ap.applicable_area_id = a.id
    JOIN cp_cases_counsel c ON c.suitno = ap.suitno
    WHERE c.counsel_id = 54
    AND a.id NOT IN (
        SELECT DISTINCT s.applicable_area_id
        FROM supreme_applicable_areas s
        JOIN cp_cases_counsel c ON c.suitno = s.suitno
        WHERE c.counsel_id = 54
    )
) AS combined
GROUP BY area, id
ORDER BY area ASC;

如果不需要设置优先级，只要保证同一区域的两类统计值不同时出现，去掉第二个子查询里的NOT IN过滤条件即可。

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