关系表查询计数异常及IN子句过滤问题排查与解决

嘿，我来帮你拆解下这个问题——从你的描述来看，问题主要出在表关联的逻辑和计数的准确性上，尤其是加了WHERE条件后，LEFT JOIN的特性被破坏，导致结果和预期不符。

先唠唠常见的坑 #

我猜你的原始查询大概是类似这样的（毕竟很多人刚接触多表关联都会踩这个坑）：

SELECT d.id AS doc_id, COUNT(x.scod_x) AS scod_count
FROM d
LEFT JOIN x ON d.id = x.doc_id
-- 加条件时会加这行
WHERE a.ver_a IN ('AA') AND b.ver_b IN ('BA')
GROUP BY d.id;

这里会踩两个大雷：

1. 不带WHERE时计数不准：如果d和x是一对多关联，再加上关联a、b表，会产生重复行——比如一个doc_id对应3条x记录，同时对应2条a记录，关联后就会变成6行，这时候COUNT(x.scod_x)统计的是6，而不是你要的3。
2. 加WHERE后结果丢了：当你在WHERE里过滤a、b的字段时，LEFT JOIN直接变成了INNER JOIN——那些不满足a.ver_a='AA'或b.ver_b='BA'的doc_id直接被踢出去了，而不是保留下来显示计数为0。

给你两个针对性的解决办法 #

1. 先统计再关联（解决不带WHERE的计数错误） #

要统计x表中每个doc_id的真实记录数，别直接关联后计数，先给x表分组统计好，再关联回d表：

SELECT d.id AS doc_id, COALESCE(x_stats.scod_count, 0) AS scod_count
FROM d
LEFT JOIN (
    -- 先在x表内部统计每个doc_id的scod_x数量
    SELECT doc_id, COUNT(scod_x) AS scod_count
    FROM x
    GROUP BY doc_id
) x_stats ON d.id = x_stats.doc_id;

这样不管其他表怎么关联，计数都是x表的真实记录数，不会被重复行干扰。

2. 把过滤条件放对地方（解决带WHERE的结果缺失） #

当需要加a、b表的过滤条件时，别把条件写在WHERE里，要写在JOIN的ON子句里——这样才能保住LEFT JOIN的特性，不会把d表中不满足条件的记录删掉：

SELECT d.id AS doc_id, COALESCE(x_stats.scod_count, 0) AS scod_count
FROM d
LEFT JOIN (
    SELECT x.doc_id, COUNT(x.scod_x) AS scod_count
    FROM x
    -- 把a、b的关联和过滤条件都放在这里
    JOIN a ON x.a_id = a.id AND a.ver_a IN ('AA')
    JOIN b ON x.b_id = b.id AND b.ver_b IN ('BA')
    GROUP BY x.doc_id
) x_stats ON d.id = x_stats.doc_id;

如果a、b表是和d表关联的，那可以这么写：

SELECT d.id AS doc_id, COALESCE(COUNT(DISTINCT x.scod_x), 0) AS scod_count
FROM d
-- 把过滤条件放到ON里，保留LEFT JOIN
LEFT JOIN a ON d.a_id = a.id AND a.ver_a IN ('AA')
LEFT JOIN b ON d.b_id = b.id AND b.ver_b IN ('BA')
LEFT JOIN x ON d.id = x.doc_id
-- 如果要求必须同时满足a、b的条件，再加这个WHERE
WHERE a.id IS NOT NULL AND b.id IS NOT NULL
GROUP BY d.id;

这里用COUNT(DISTINCT x.scod_x)是为了避免多表关联产生的重复行让计数变大，如果你的scod_x是唯一的，直接用COUNT(x.scod_x)就行。

最后划个重点 #

• 统计子表记录数：优先用子查询分组统计，再关联主表，避开重复行的坑。
• LEFT JOIN的过滤条件：一定要放在ON里，放WHERE里就变成INNER JOIN了，会丢数据。
• 用COALESCE()：确保没有匹配记录的doc_id显示0，而不是NULL，符合预期结果。

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