我的情况是,我需要为每个问题选择在sql中正确回答该问题的用户数量我的表格看起来像
table 1: questionnaire_question
id - question_text - thecorrectAnswer
table 2 :questionnaire_answers
id - user_id - question_id - user_answer - correct
正确列的值为 0 或 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;
您可以联接表并count
检查正确性的case
表达式:
SELECT q.id, cnt
FROM questionnaire_question q
JOIN (SELECT question_id, COUNT(CASE correct WHEN 1 THEN 1 END) AS cnt
FROM questionnaire_answers
GROUP BY question_id) a ON q.id = a.question_id
如果问题没有正确答案,则使用子查询,它将返回 0
试试这个:
SELECT qq.id,
COALESCE((SELECT COUNT(*)
FROM questionnaire_answers qa
WHERE qa.question_id = qq.question_id
AND qa.correct = 1), 0)
FROM questionnaire_question qq
select question_id, count(user_id)
from questionnaire_answers
where correct = 1
group by question_id
无需联接,因为您已经通过包含正确的列对架构进行了非规范化。