SQL关联查询计数异常及需求适配问题咨询

为什么当前查询会统计整个Opportunity的任务？ #

你的查询逻辑里，LEFT JOIN task ON ocr.opportunity_id = task.opportunity_id直接把该Opportunity下所有任务都拉进来了，后续的LEFT JOIN task_contact tc并没有过滤出当前Contact对应的任务——这个关联是在所有任务之后做的，相当于把所有任务和它们的联系人匹配，但没限定到目标contact_id，最终就统计了整个Opportunity的任务总数，而非该Contact被分配的任务。另外，GROUP BY里包含了task.status，这会让同一个Opportunity下不同状态的任务拆成不同行，也不符合你想要的聚合结果。

修改后的查询语句 #

要实现需求，我们需要先从opportunity_contact拿到该Contact关联的所有Opportunity，再精准关联到该Contact被分配的任务（通过task_contact锁定目标contact_id），最后按contact_id和opportunity_id聚合统计：

SELECT
  ocr.contact_id,
  ocr.opportunity_id,
  COUNT(tc.task_id) AS total_tasks_count,
  SUM(CASE WHEN t.status = 'In Progress' THEN 1 ELSE 0 END) AS tasks_in_progress_count,
  SUM(CASE WHEN t.status = 'In Review' THEN 1 ELSE 0 END) AS tasks_in_review_count,
  SUM(CASE WHEN t.status = 'Completed' THEN 1 ELSE 0 END) AS tasks_completed_count
FROM opportunity_contact ocr
LEFT JOIN task_contact tc 
  ON ocr.contact_id = tc.contact_id
LEFT JOIN task t 
  ON tc.task_id = t.id AND t.opportunity_id = ocr.opportunity_id
WHERE ocr.contact_id = '1'
GROUP BY ocr.contact_id, ocr.opportunity_id;

关键调整点： #

• 关联顺序优化：先从opportunity_contact出发，通过task_contact关联到当前Contact的任务，而非先拉取整个Opportunity的任务，这样只会拿到该Contact被分配的任务记录。
• 关联条件强化：在JOIN task时加上t.opportunity_id = ocr.opportunity_id，确保任务属于当前关联的Opportunity，避免跨Opportunity的任务被错误统计。
• 分组逻辑修正：只按contact_id和opportunity_id分组，让每个Opportunity对应一行结果，匹配你期望的输出格式。
• 统计方式简化：用COUNT(tc.task_id)统计总任务数更直观，因为tc.task_id只有在该Contact有分配任务时才不为NULL。

结果验证 #

针对你的示例场景，这个查询会返回：

完全符合你想要的结果：既统计了该Contact被分配的任务，也包含了关联但未分配任务的Opportunity。

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