mysql - 如何使用 in 进行子查询，使表中不存在的值计数为 0

Question

我需要帮助来编写正确的查询。我们有一个调查数据库，其中有一个问题的 OfferedAnswers 表，该表具有 question_id 和 answer_id 的映射。

提供的答案:

-----------------------
answer_id   | question_id
-----------------------
1           |  1
-----------------------
2           |  1
-----------------------
3           |  1
-----------------------
4           |  1
-----------------------

另一个是 FeedbackQuestionAnswer 表，它记录了用户投票的 question_id 和 answer_id 的响应。

反馈问题答案:

-----------------------
question_id   | answer_id
-----------------------
1             |  1
-----------------------
1             |  2
-----------------------
1             |  1
-----------------------


 select    FeedbackQuestionAnswer.answer_Id, 
           count(FeedbackQuestionAnswer.answer_Id) as count 
           from dEbill.FeedbackQuestionAnswer  
           where FeedbackQuestionAnswer.answer_Id in
           (select answer_Id 
                   from dEbill.OfferedAnswers 
                   where question_Id = 1)
           group by FeedbackQuestionAnswer.answer_Id

在上面的例子中，输出是:

    -----------------------
    answer_id   | count
    -----------------------
    1           |  2
    -----------------------
    2           |  1
    -----------------------

而我想要:

    -----------------------
    answer_id   | count
    -----------------------
    1           |  2
    -----------------------
    2           |  1
    -----------------------
    3           |  0
    -----------------------
    4           |  0
    -----------------------

我尝试使用 OfferedAnswer.answer_Id 而不是 FeedbackQuestionAnswer.answer_Id，但出现范围错误。我也试过 right join 但没有成功。

Answer 1

只需使用左连接，并计算第二个表中的非 NULL 条目:

SELECT
    t1.answer_id,
    COUNT(t2.answer_id) AS count
FROM OfferedAnswers t1
LEFT JOIN FeedbackQuestionAnswer t2
    ON t1.answer_id = t2.answer_id
WHERE
    t1.question_id = 1
GROUP BY
    t1.answer_id
ORDER BY
    t1.answer_id;

Demo