多对多关系取数：按office_id统计案件总数及对应总成本

业务背景 #

• 涉及表：category、case、cost
• 关联关系：
  • category.category_id = case.category_id
  • case.case_number = cost.case_number
• 核心字段：category表存储office_id，cost表存储每条成本记录的total_cost
• 统计需求：按office_id分组，统计每个办公机构对应的案件总数量、累计总成本

原始SQL问题分析 #

最初编写的SQL存在两处核心问题：

1. 语法不规范：SELECT子句出现了case_number字段，该字段既不在GROUP BY分组维度中，也没有被聚合函数包裹，在开启ONLY_FULL_GROUP_BY校验的数据库（如MySQL 5.7+默认开启）中会直接执行报错
2. 统计结果错误：直接使用count(*)统计案件数，若单个案件对应多条成本记录，三表关联后会生成多行重复案件数据，导致案件计数结果远大于实际值

原始SQL代码：

select cm_c_d.case_number,cm_c.office_id,count(*) as case_count from categories as cm_c 
join case as cm_c_d on cm_c.category_id = cm_c_d.category_id 
join cost on cm_c_d.case_number = cost.case_number group by office_id;

调整后SQL正确性说明 #

后续优化的SQL已经解决了上述问题，逻辑正确：

• 用count(DISTINCT cm_costs.case_number)统计案件数，自动过滤了单案件关联多条成本记录导致的重复计数
• 用SUM(total_charge)累加所有关联的成本金额，得到对应办公机构的累计总成本

调整后SQL代码：

select cm_c.office_id
     , count(DISTINCT cm_costs.case_number) as case_count
     , SUM(total_charge) AS overall_cost
  from cm_categories as cm_c
  JOIN cm_case_details as cm_c_d
    on cm_c.category_id = cm_c_d.category_id
  join cm_costs
    on cm_c_d.case_number = cm_costs.case_number
 group by cm_c.office_id
;

可选性能优化方案 #

如果数据量较大，count(DISTINCT)执行效率偏低，可以先对成本表按案件维度预聚合，再关联上游表查询，避免去重操作带来的性能损耗：

SELECT cm_c.office_id,
       COUNT(1) AS case_count,
       SUM(t.case_total_cost) AS overall_cost
FROM cm_categories cm_c
JOIN cm_case_details cm_c_d ON cm_c.category_id = cm_c_d.category_id
JOIN (
    -- 先按案件维度聚合单案件总成本
    SELECT case_number, SUM(total_charge) AS case_total_cost
    FROM cm_costs
    GROUP BY case_number
) t ON cm_c_d.case_number = t.case_number
GROUP BY cm_c.office_id

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