MySQL查询如何按主键id仅返回1条结果？多表JOIN重复数据问题求助

问题根因 #

• 核心错误是JOIN关联条件不完整：你当前写的ON people.id属于布尔判断逻辑，只要people.id不为空就判定为关联成立，等效于ON 1=1，直接触发笛卡尔积：people表中符合id=27的1条记录，会和people_tags表的所有记录逐一拼接，返回结果的条数就等于people_tags表的总记录数，你拿到78条结果说明people_tags表当前共存储了78条标签数据。
• 额外注意：两张表的关联逻辑需要确认外键对应关系，从你给出的表结构来看，people表的id是人员主键，people_tags表的id是标签主键，如果people_tags没有单独存储people_id这类外键字段，大概率是设计时直接用people_tags的id字段关联people表的id。同时你写的RIGHT JOIN在此场景下没有意义，WHERE条件已经限定了people.id=27，会自动过滤掉people.id为空的记录，实际效果和INNER JOIN一致。

修复方案 #

方案1：补全关联条件查询（适用1人对应1条标签记录的场景，可直接返回1条结果） #

SELECT 
    people.name,
    people.id,
    mn,
    people_tags.sex,
    people_tags.family
FROM
    people
INNER JOIN
    people_tags ON people.id = people_tags.id
WHERE
    people.id = '27';

方案2：强制返回1条结果（适用1人对应多条标签、只需要任意1条匹配结果的场景） #

SELECT 
    people.name,
    people.id,
    mn,
    people_tags.sex,
    people_tags.family
FROM
    people
INNER JOIN
    people_tags ON people.id = people_tags.id
WHERE
    people.id = '27'
LIMIT 1;

方案3：合并多标签内容返回1条人员记录（适用需要保留所有标签信息的场景） #

SELECT 
    people.name,
    people.id,
    mn,
    GROUP_CONCAT(DISTINCT people_tags.sex) AS sex,
    GROUP_CONCAT(DISTINCT people_tags.family) AS family
FROM
    people
INNER JOIN
    people_tags ON people.id = people_tags.id
WHERE
    people.id = '27'
GROUP BY people.id;

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