三张表关联查询问题求助（以中间主键为关联核心）

嘿，我太懂你这种多表关联时踩坑的感觉了——尤其是当两张表都和主表是一对多关系的时候，很容易因为笛卡尔积搞出错误的统计结果！先帮你理清楚你的表结构，然后一步步解决这个对比总数量的需求。

你的表结构梳理 #

先把你给出的表数据整理得更清晰一点（顺便提一句，case表第三行的id写了pizza，应该是输入笔误？不过先按你提供的内容来）：

• case表（主表，id为主键）：

• registration表（与case是一对多，id为外键关联case.id）：

• bagged表（与case是一对多，case_id为外键关联case.id）：

你遇到的核心问题 #

如果你直接把三张表关联查询，比如写：

SELECT c.id, SUM(r.quantity), SUM(b.kg_bagged)
FROM `case` c
JOIN registration r ON c.id = r.id
JOIN bagged b ON c.id = b.case_id
GROUP BY c.id;

会得到错误的结果——因为case 155有2条registration记录和2条bagged记录，关联后会生成2*2=4条数据，导致SUM(r.quantity)变成300+200+300+200=1000，SUM(b.kg_bagged)变成123+90+123+90=426，这显然不是你要的真实总和！

正确的解决方案：先分组统计，再关联 #

解决思路很简单：先分别对registration和bagged表按case的id分组统计总和，再把统计结果和主表case关联，这样就不会产生笛卡尔积了。

写法一：子查询关联（适用于所有主流数据库）

SELECT 
    c.id,
    c.date_closed,
    -- 用COALESCE处理没有对应记录的情况，显示0而不是NULL
    COALESCE(r.total_quantity, 0) AS total_quantity,
    COALESCE(b.total_kg, 0) AS total_kg_bagged
FROM 
    `case` c
-- 左关联确保即使没有对应记录也能显示主表数据
LEFT JOIN (
    SELECT 
        id,
        SUM(quantity) AS total_quantity
    FROM 
        registration
    GROUP BY 
        id
) r ON c.id = r.id
LEFT JOIN (
    SELECT 
        case_id,
        SUM(kg_bagged) AS total_kg
    FROM 
        bagged
    GROUP BY 
        case_id
) b ON c.id = b.case_id;

写法二：CTE（公共表表达式，适用于支持的数据库如PostgreSQL、MySQL 8.0+等）

这种写法更清晰，可读性更强：

WITH reg_totals AS (
    -- 先统计每个case的总注册数量
    SELECT 
        id,
        SUM(quantity) AS total_quantity
    FROM registration
    GROUP BY id
),
bagged_totals AS (
    -- 再统计每个case的总包装重量
    SELECT 
        case_id,
        SUM(kg_bagged) AS total_kg
    FROM bagged
    GROUP BY case_id
)
-- 最后关联主表和两个统计结果
SELECT 
    c.id,
    c.date_closed,
    COALESCE(rt.total_quantity, 0) AS total_quantity,
    COALESCE(bt.total_kg, 0) AS total_kg_bagged
FROM `case` c
LEFT JOIN reg_totals rt ON c.id = rt.id
LEFT JOIN bagged_totals bt ON c.id = bt.case_id;

查询结果验证 #

运行上面的语句后，你会得到准确的统计结果：

完美符合你对比总数量的需求！

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