多表Left Join返回冗余行求助：基于resort_id关联3张表

我明白你遇到的困扰了——明明每张表对应指定resort_id只有1条数据，但用左连接查询后却出现了冗余结果（要么是主表字段重复出现，要么是返回了超出预期的行数）。这其实是SQL多表连接的特性导致的，咱们一步步来解决：

为什么会出现冗余？ #

当你用LEFT JOIN同时连接多个与主表关联的子表时，数据库会生成笛卡尔积组合。比如如果resort_activities有1条数据、resort_gallery有1条数据，结果会是1行（1×1）；但如果任意子表有多条匹配数据，行数会变成子表行数的乘积，主表字段就会重复出现在每一行里，形成冗余。

结合你给出的表结构（每张表仅1条匹配数据），原查询其实应该返回1行包含三个表所有字段的数据。如果你觉得这是“冗余”，大概率是你期望的是分别获取三个表的独立数据，而不是合并成一行的结果。

解决方案 #

根据你的需求，我提供两种常用的解决方式：

方式1：分别获取三个表的独立数据（返回多行，每行对应一个表的数据） #

如果想要得到三个表各自的记录，而非合并后的行，可以用UNION ALL组合三个独立查询（用NULL填充不存在的字段，保证每个查询的字段数和类型一致）：

-- 标记数据来源，同时返回各表字段
SELECT 
    'resort' AS data_source,
    resort_id,
    resort_name,
    NULL AS activity_id,
    NULL AS gallery_id
FROM resorts 
WHERE resort_id = $id

UNION ALL

SELECT 
    'activity' AS data_source,
    resort_id,
    NULL AS resort_name,
    activity_id,
    NULL AS gallery_id
FROM resort_activities 
WHERE resort_id = $id

UNION ALL

SELECT 
    'gallery' AS data_source,
    resort_id,
    NULL AS resort_name,
    NULL AS activity_id,
    gallery_id
FROM resort_gallery 
WHERE resort_id = $id;

这个查询会返回3行，每行对应一个表的匹配数据，通过data_source字段可以清晰区分数据来自哪个表。

方式2：合并成一行无冗余的结构化结果 #

如果想要把三个表的数据整合到一行里，避免主表数据重复，可以用聚合函数结合JSON格式把子表数据打包成数组：

SELECT 
    r.resort_id,
    r.resort_name,
    -- 将resort_activities的所有数据打包成JSON数组
    JSON_ARRAYAGG(ra.*) AS activities,
    -- 将resort_gallery的所有数据打包成JSON数组
    JSON_ARRAYAGG(rg.*) AS galleries
FROM resorts r
LEFT JOIN resort_activities ra ON r.resort_id = ra.resort_id
LEFT JOIN resort_gallery rg ON r.resort_id = rg.resort_id
WHERE r.resort_id = $id
-- 按主表字段分组，确保只返回一行主表数据
GROUP BY r.resort_id, r.resort_name;

这个查询会返回1行数据，其中activities和galleries是包含对应子表数据的JSON数组，即使后续子表新增多条数据，也只会在数组里添加元素，不会产生冗余行。

额外检查 #

如果你的原查询确实返回了多行（而非1行），那可能是你描述的表结构有误——比如resort_activities或resort_gallery里存在多条resort_id=1的数据。可以先单独查询这两个表确认：

SELECT * FROM resort_activities WHERE resort_id = $id;
SELECT * FROM resort_gallery WHERE resort_id = $id;

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