多表关联查询时如何获取正确的COUNT与SUM统计结果

核心错误原因 #

• 多级LEFT JOIN直接关联原始表时，多层一对多关系会生成笛卡尔积，上层记录被重复计数是统计值翻倍的核心原因。比如table1关联3条table2记录、同时关联2条table3链路的记录时，直接JOIN后会生成3*2=6条重复的table1记录，聚合时SUM/COUNT结果自然不符合预期。
• 原SQL存在逻辑错误：子查询非法引用外层表table2的deleted_at字段、表关联字段写错（table4关联table3时误用visa_id，应为table3_id）、CASE WHEN与COUNT搭配逻辑错误（COUNT会统计所有非空值，写ELSE 0会把不符合条件的记录也计入统计）。

正确实现逻辑 #

所有统计分支先按table1_id维度预聚合为单行结果，再关联到主表table1，完全避免关联时产生笛卡尔积。

可用查询代码 #

SELECT
  t1.id AS table1_id,
  t1.name AS table1_name,
  COALESCE(t2_stats.sum_count, 0) AS sum_table2_count,
  COALESCE(t4_stats.sum_count, 0) AS sum_table4_count,
  COALESCE(t6_stats.sum_table5_count, 0) AS sum_table5_count,
  COALESCE(t6_stats.status_3_count, 0) AS status_3_count,
  COALESCE(t6_stats.status_not_3_count, 0) AS status_not_3_count
FROM table1 t1
-- 预聚合table2统计值
LEFT JOIN (
  SELECT
    table1_id,
    SUM(count::int) AS sum_count
  FROM table2
  WHERE deleted_at IS NULL
  GROUP BY table1_id
) t2_stats ON t1.id = t2_stats.table1_id
-- 预聚合table4统计值
LEFT JOIN (
  SELECT
    t3.table1_id,
    SUM(t4.count::int) AS sum_count
  FROM table3 t3
  LEFT JOIN table4 t4 
    ON t3.id = t4.table3_id 
    AND t4.deleted_at IS NULL
  WHERE t3.deleted_at IS NULL
  GROUP BY t3.table1_id
) t4_stats ON t1.id = t4_stats.table1_id
-- 预聚合table5、table6统计值
LEFT JOIN (
  SELECT
    t3.table1_id,
    SUM(t5.count::int) AS sum_table5_count,
    COUNT(CASE WHEN t6.status = 3 THEN 1 END) AS status_3_count,
    COUNT(CASE WHEN t6.status != 3 THEN 1 END) AS status_not_3_count
  FROM table3 t3
  LEFT JOIN table4 t4 
    ON t3.id = t4.table3_id 
    AND t4.deleted_at IS NULL
  LEFT JOIN table5 t5 
    ON t4.id = t5.table4_id 
    AND t5.deleted_at IS NULL
  LEFT JOIN table6 t6 
    ON t5.id = t6.table5_id 
    AND t6.deleted_at IS NULL
  WHERE t3.deleted_at IS NULL
  GROUP BY t3.table1_id
) t6_stats ON t1.id = t6_stats.table1_id;

结果验证 #

你提供的示例数据运行后返回结果如下，完全符合预期：

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