多次自连接MySQL表导致计数异常的问题求助

问题原因 #

你当前的查询通过两次LEFT JOIN分别关联筛选出timeout和confirmed的子表，会产生笛卡尔积问题：

• 对于user_id=7，timeoutTable有2条记录，confirmedTable有1条记录，关联后会生成2×1=2条重复记录。
• COUNT(timeoutTable.user_id)和COUNT(confirmedTable.user_id)会统计这2条记录中所有非空的user_id，因此两个计数都返回2，不符合预期。
• 另外GROUP BY中包含timeoutTable.user_id和confirmedTable.user_id属于冗余，可能导致分组逻辑混乱。

解决方案1：条件聚合（推荐） #

直接在主查询中使用CASE语句配合COUNT实现条件统计，避免笛卡尔积，代码更简洁高效：

select 
  Signups.user_id,
  COUNT(CASE WHEN Confirmations.action = 'timeout' THEN 1 END) as timeoutCount,
  COUNT(CASE WHEN Confirmations.action = 'confirmed' THEN 1 END) as confirmedCount
from Signups
left join Confirmations on Signups.user_id = Confirmations.user_id
group by Signups.user_id

原理：CASE语句仅在满足条件时返回1，否则返回NULL；COUNT函数会自动忽略NULL值，从而准确统计对应状态的记录数。

解决方案2：子查询预统计 #

先在子查询中按user_id分组统计各状态的数量，再关联主表，避免笛卡尔积：

select 
  Signups.user_id,
  COALESCE(timeoutTable.timeoutCount, 0) as timeoutCount,
  COALESCE(confirmedTable.confirmedCount, 0) as confirmedCount
from Signups
left join (
  select user_id, COUNT(*) as timeoutCount 
  from Confirmations 
  where action = 'timeout'
  group by user_id
) as timeoutTable on Signups.user_id = timeoutTable.user_id
left join (
  select user_id, COUNT(*) as confirmedCount 
  from Confirmations 
  where action = 'confirmed'
  group by user_id
) as confirmedTable on Signups.user_id = confirmedTable.user_id
group by Signups.user_id, timeoutTable.timeoutCount, confirmedTable.confirmedCount

原理：子查询提前完成分组统计，关联时每个user_id只会匹配一条统计结果，不会产生重复记录；COALESCE用于将无匹配时的NULL转换为0，符合输出要求。

验证结果 #

执行上述任一方案后，user_id=7的记录会正确返回timeoutCount=2、confirmedCount=1，其他用户的计数也符合预期。

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