SQL多表关联统计组织私有话题、管理员与普通用户数量报错问题

• 同表重复关联未定义别名：两次JOIN users表未设置不同别名，数据库无法区分表实例，触发重复表名报错。
• 多维度关联产生笛卡尔积：即使添加别名，topics和users属于独立关联维度，直接JOIN会导致行数据相乘，计数逻辑混乱，出现两列用户统计数值一致的异常。

仅关联一次users表，通过条件筛选完成多指标统计，避免笛卡尔积问题，性能更优：

SELECT 
  o.name,
  COUNT(DISTINCT t.id) AS private_topic,
  COUNT(DISTINCT CASE WHEN u.type = 'admin' THEN u.id END) AS user_admin,
  COUNT(DISTINCT CASE WHEN u.type = 'standard' THEN u.id END) AS user_standard
FROM organizations o
LEFT JOIN topics t 
  ON o.id = t.org_id 
  AND t.privacy = 'private'
LEFT JOIN users u
  ON o.id = u.org_id
GROUP BY o.name;

各维度先单独统计完成后再关联，彻底避免关联计数误差，适合大数据量场景：

SELECT 
  o.name,
  COALESCE(t.private_count, 0) AS private_topic,
  COALESCE(a.admin_count, 0) AS user_admin,
  COALESCE(s.standard_count, 0) AS user_standard
FROM organizations o
LEFT JOIN (
  SELECT org_id, COUNT(*) AS private_count 
  FROM topics 
  WHERE privacy = 'private'
  GROUP BY org_id
) t ON o.id = t.org_id
LEFT JOIN (
  SELECT org_id, COUNT(*) AS admin_count 
  FROM users 
  WHERE type = 'admin'
  GROUP BY org_id
) a ON o.id = a.org_id
LEFT JOIN (
  SELECT org_id, COUNT(*) AS standard_count 
  FROM users 
  WHERE type = 'standard'
  GROUP BY org_id
) s ON o.id = s.org_id;

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