mysql - 获取每个问题，正确回答的用户数量

Question

我有这样的情况，我需要为每个问题选择在 sql 中正确回答该问题的用户数量我的 table 看起来像

table 1: questionnaire_question  
id  - question_text - thecorrectAnswer

table 2 :questionnaire_answers  
id -  user_id -  question_id -  user_answer - correct

correct 列的值为 0 或 1，表示用户回答正确与否

Answer 1

使用LEFT JOIN、GROUP BY 和COUNT

select
  q.id,
  count(distinct a.user_id) cnt  -- can be replaced with count(*) 
                                 -- if a user has only at max one correct answer
                                 -- for a given question
from questionnaire_question q
left join questionnaire_answers a
on q.id = a.question_id
where correct = 1
group by q.id;