我需要帮助来编写正确的查询。我们有一个调查数据库,其中有一个提供了question_id和answer_id映射的问题的OfferedAnswers表。
提供的答案:
-----------------------
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,但遇到范围错误。我也尝试了正确的加入,但没有成功。
只需使用左联接,然后计算第二个表中的非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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句