如何区分图书查询结果中人员的外键关联角色（作者/译者）？

嘿，这个问题的核心是你当前的查询完全没用到author和translator这两个关键的关联表——你只是把book和person做了笛卡尔积，自然没法区分每个人的身份。给你两种靠谱的解决方法：

方法一：用LEFT JOIN + CASE语句标记身份 #

这种方法通过关联身份表，判断每条记录匹配的是作者还是译者，最终新增一列明确角色：

SELECT 
    book.title,
    person.surname,
    person.name,
    CASE
        WHEN author.book_id IS NOT NULL THEN 'Author'
        WHEN translator.book_id IS NOT NULL THEN 'Translator'
        ELSE 'Unknown'
    END AS role
FROM book
LEFT JOIN author 
    ON book.book_id = author.book_id 
    AND person.person_id = author.person_id
LEFT JOIN translator 
    ON book.book_id = translator.book_id 
    AND person.person_id = translator.person_id
WHERE book.title = 'Divine Comedy'
AND (author.book_id IS NOT NULL OR translator.book_id IS NOT NULL);

说明： #

• 用LEFT JOIN关联author和translator表，确保不会漏掉任何关联人员
• CASE语句通过判断关联表是否有匹配数据，标记对应的角色
• 最后加的AND条件可以过滤掉和当前图书无关的人员（避免笛卡尔积带来的无效数据）

方法二：用UNION ALL分别查询后合并 #

这种方法逻辑更直观，分别查询作者和译者数据，再合并结果并标记角色：

SELECT 
    book.title,
    person.surname,
    person.name,
    'Author' AS role
FROM book
JOIN author ON book.book_id = author.book_id
JOIN person ON author.person_id = person.person_id
WHERE book.title = 'Divine Comedy'

UNION ALL

SELECT 
    book.title,
    person.surname,
    person.name,
    'Translator' AS role
FROM book
JOIN translator ON book.book_id = translator.book_id
JOIN person ON translator.person_id = person.person_id
WHERE book.title = 'Divine Comedy';

说明： #

• 两个独立查询分别获取作者和译者数据，各自添加明确的role标记
• 使用UNION ALL而非UNION，避免自动去重（如果有人既是作者又是译者，会保留两条记录，更符合实际场景）

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