构造where子句以包含null / 0值时出现问题 [英] Problem constructing where clause to include null/0 values

问题描述

我有一个应用程序，用户可以在其中进行测试（由问题和答案组成）。

I have an application where users can take tests (which are composed of questions and answers).

我正在尝试构建一个返回计数为针对特定老师和测试按问题分组的答案。
问题是我希望查询返回0个无答案的问题。

I'm trying to construct a query that returns a count of answers grouped by question, for a specific teacher and test. The problem is I would like the query to return 0 count for questions with no answers.

• id


• question_id


• test_id


• student_id

• id
• question_id
• test_id
• student_id

• id

• student_id


• teacher_id

• id

   SELECT COUNT(answers.id) AS rcount,
          questions.id 
     FROM "questions"
LEFT JOIN answers ON answers.question_id = questions.id
LEFT JOIN teacher_students ON teacher_students.student_id = answers.student_id
    WHERE (questions.test_id = 1)  
      AND (teacher_students.teacher_id = 1)
 GROUP BY questions.id 
 ORDER BY questions.id

输出

Output

 rcount | question_id 
--------+----
      4 | 1
      2 | 3

所需的输出

 rcount | question_id 
--------+----
      4 | 1
      0 | 2
      2 | 3
      0 | 4

如果我删除 teacher_students.teacher_id = 1 ，返回的问题数是正确的，但计数不是正确的。

If I remove teacher_students.teacher_id = 1, the number of questions returned is correct, but the count is not.

推荐答案

移动 teacher_students .teacher_id = 1 从 WHERE 子句到加入的 ON 子句的支票。

Move the teacher_students.teacher_id = 1 check from the WHERE clause to the joining ON clause.

当在 LEFT JOIN 右侧引用了表的条件时， ，则LEFT JOIN被取消，它充当 INNER JOIN 。

When a condition that refers to a table in the right side of a LEFT JOIN is put in the WHERE clause, the LEFT JOIN is cancelled and it acts as an INNER JOIN.

SELECT count(teacher_students.student_id) AS rcount      <--- changed
     , questions.id 
FROM "questions"
  LEFT JOIN answers 
    ON answers.question_id = questions.id
  LEFT JOIN teacher_students 
    ON teacher_students.student_id = answers.student_id
    AND teacher_students.teacher_id = 1
WHERE questions.test_id = 1

GROUP BY questions.id 
ORDER BY questions.id

这篇关于构造where子句以包含null / 0值时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！