SQL统计同时关联sentences和vocabulary的topics数量 解决关联计数错误

问题原因说明 #

你原有查询计数不准的核心原因是对两个关联表同时JOIN时会产生笛卡尔积：比如某topic下有3条符合条件的sentences、2条符合条件的vocabulary，JOIN后会生成3*2=6条中间数据，直接count(s.id)、count(v.id)得到的结果都是6，和实际数量不符。

解决方案 #

1. 简单修改版（适合小数据量场景）

用DISTINCT对关联表的主键去重后计数即可：

SELECT
  t.id AS topic_id,
  COUNT(DISTINCT s.id) AS total_sentences,
  COUNT(DISTINCT v.id) AS total_vocabulary
FROM topics t
LEFT JOIN sentences s 
  ON s.topic_id = t.id AND s.locale_id = 1
LEFT JOIN vocabulary v 
  ON v.topic_id = t.id AND v.locale_id = 1
GROUP BY t.id
-- 筛选同时存在两类关联数据的topic
HAVING total_sentences > 0 AND total_vocabulary > 0
ORDER BY t.id;

2. 高性能明细输出版（适合大数据量场景）

提前对两个关联表做单独聚合，再和topics表关联，避免产生笛卡尔积冗余数据：

SELECT 
  t.id AS topic_id,
  s.cnt AS total_sentences,
  v.cnt AS total_vocabulary
FROM topics t
-- 先统计每个topic的sentences数量
INNER JOIN (
  SELECT topic_id, COUNT(id) AS cnt
  FROM sentences
  WHERE locale_id = 1
  GROUP BY topic_id
) s ON s.topic_id = t.id
-- 再统计每个topic的vocabulary数量
INNER JOIN (
  SELECT topic_id, COUNT(id) AS cnt
  FROM vocabulary
  WHERE locale_id = 1
  GROUP BY topic_id
) v ON v.topic_id = t.id
ORDER BY t.id;

该方案用INNER JOIN自动过滤了只有单表关联数据的topic，不需要额外加筛选条件，执行效率更高。

3. 最高性能计数版（仅需要统计符合条件的topic总数量时使用）

如果不需要输出每个topic的具体关联数量，直接用EXISTS查询性能最优：

SELECT COUNT(*) AS qualified_topic_count
FROM topics t
WHERE EXISTS (
  SELECT 1 FROM sentences s 
  WHERE s.topic_id = t.id AND s.locale_id = 1
) AND EXISTS (
  SELECT 1 FROM vocabulary v 
  WHERE v.topic_id = t.id AND v.locale_id = 1
);

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