MariaDB中统计特定城镇ID对应的提示（hint）与测验（quiz）数量：单条SQL实现问题排查

不用拆分两条SQL！单条语句完全能得到你想要的正确统计结果。

先说说你当前SQL出问题的原因：当你同时左连db_quiz和db_hint时，两个表的记录会形成笛卡尔积——1条hint记录会和2条quiz记录分别配对，最终返回2条合并后的行。这时候count(q.id)和count(h.id)都会统计到2条（因为h.id在两行里都存在），结果自然就和预期不符了。

下面给你几种适合MariaDB的解决办法，按需选择：

方案1：用COUNT(DISTINCT)快速修正 #

最简单的办法就是在count里加上DISTINCT，确保每个唯一的id只被统计一次：

SELECT 
  COUNT(DISTINCT q.id) AS quiz_count,
  COUNT(DISTINCT h.id) AS hint_count
FROM `db_town` t 
LEFT JOIN `db_quiz` q ON t.id = q.town_id 
LEFT JOIN `db_hint` h ON t.id = h.town_id 
WHERE t.id = 1 
GROUP BY t.id

这个方案上手快，适合表主键唯一的场景，能直接修正你的原SQL。

方案2：子查询预先统计（性能更优） #

如果数据量较大，推荐先分别统计每个城镇的测验和提示数量，再和城镇表关联，从根源避免笛卡尔积：

SELECT
  COALESCE(q.quiz_count, 0) AS quiz_count,
  COALESCE(h.hint_count, 0) AS hint_count
FROM `db_town` t
LEFT JOIN (
  SELECT town_id, COUNT(id) AS quiz_count 
  FROM `db_quiz` 
  GROUP BY town_id
) q ON t.id = q.town_id
LEFT JOIN (
  SELECT town_id, COUNT(id) AS hint_count 
  FROM `db_hint` 
  GROUP BY town_id
) h ON t.id = h.town_id
WHERE t.id = 1

这里用COALESCE是为了防止某个城镇没有对应测验/提示时返回NULL，改成返回0更符合统计需求。

方案3：SUM(CASE)灵活统计 #

如果需要加额外条件统计（比如只统计特定状态的测验），可以用这种方式，记得配合DISTINCT避免重复计数：

SELECT
  SUM(DISTINCT CASE WHEN q.id IS NOT NULL THEN 1 ELSE 0 END) AS quiz_count,
  SUM(DISTINCT CASE WHEN h.id IS NOT NULL THEN 1 ELSE 0 END) AS hint_count
FROM `db_town` t
LEFT JOIN `db_quiz` q ON t.id = q.town_id
LEFT JOIN `db_hint` h ON t.id = h.town_id
WHERE t.id = 1
GROUP BY t.id

这几种方案都能得到你预期的结果：quiz_count=2，hint_count=1。其中方案2在数据量大的时候性能更好，因为它先把统计结果算出来，再做关联，减少了数据处理量。

内容的提问来源于stack exchange，提问作者Lukáš Michalec