如何在SELECT的CASE语句中合并行以获取期望查询结果？

看起来你遇到的核心问题是：直接关联answers表时，每个答案会生成单独的行，导致正确答案和错误答案分散在不同记录里，无法让每个nlp_terms.word对应一行且包含所有正确/错误答案。下面是具体的解决方案：

核心思路 #

1. 先单独提取每个问题的正确答案（通过匹配question.answer_id和answers.id）。
2. 对每个问题的错误答案进行编号，再通过条件聚合将前两个错误答案转换为列（incorrect_answer_1、incorrect_answer_2）。
3. 最后将questions、nlp_terms、正确答案、聚合后的错误答案关联起来，得到期望的输出结构。

修正后的SQL查询 #

SELECT
    q.question_id,
    q.question,
    q.answer_id,
    nt.word,
    correct_ans.answer,
    incorrect_ans.incorrect_answer_1,
    incorrect_ans.incorrect_answer_2
FROM questions q
-- 关联nlp_terms，保留每个word的行
JOIN nlp_terms nt ON q.question_id = nt.question_id
-- 获取当前问题的正确答案
JOIN answers correct_ans ON q.answer_id = correct_ans.id
-- 获取当前问题的前两个错误答案（已聚合为列）
JOIN (
    SELECT
        question_id,
        -- 取第一个错误答案
        MAX(CASE WHEN rn = 1 THEN answer END) AS incorrect_answer_1,
        -- 取第二个错误答案
        MAX(CASE WHEN rn = 2 THEN answer END) AS incorrect_answer_2
    FROM (
        SELECT
            question_id,
            answer,
            -- 给每个问题的错误答案编号（排除正确答案）
            ROW_NUMBER() OVER(
                PARTITION BY question_id 
                ORDER BY id
            ) AS rn
        FROM answers ans
        WHERE ans.id != (
            SELECT answer_id 
            FROM questions 
            WHERE question_id = ans.question_id
        )
    ) numbered_incorrect
    GROUP BY question_id
) incorrect_ans ON q.question_id = incorrect_ans.question_id
WHERE q.question_id = '1';

代码解释 #

• 内层子查询numbered_incorrect：给每个问题的错误答案（排除正确答案的记录）按id排序并编号，这样每个错误答案有唯一的序号rn。
• 中间子查询incorrect_ans：使用MAX(CASE...)的条件聚合，把序号为1和2的错误答案分别提取为两个独立列，实现错误答案的合并。
• 外层关联：将问题表、nlp术语表、正确答案、聚合后的错误答案关联，确保每个word对应一行，且正确/错误答案都填充在同一行中。

如果你的数据库支持PIVOT语法（比如SQL Server、Oracle），也可以用PIVOT替代条件聚合，但上面的条件聚合写法是跨数据库通用的。

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