mySQL SELECT 使用 LEFT JOIN，返回值或 NULL WHERE 列在右表中指定

Question

我是一名刚接触 php|mySQL 的高中教师，我正在尝试开发一个查询来显示我学生的考试成绩。挑战在于显示给定测试的分数，如果没有发布分数则显示 NULL。我正在尝试将两个步骤合并为一个查询:

1. 根据提交的测试结果创建特定类(class)的学生名单。
2. 显示每个学生在该测试中的分数，或者，如果由于他们未参加测试而导致分数不存在，则在其旁边显示他们的姓名和 NULL。

带有每个评估记录的“测试”表包含以下列:

test_id
assessment_id
username
score
maxScore
stopTime

包含每个学生记录的“用户”表包含以下列:

user_id
schoolId
username
first_name
last_name
section

期望的结果:

username | last_name | first_name | schoolId | assessment_id | score | maxScore
------------------------------------------------------------------------------------
jadams1  | Adams     | Joe        | 111111   | 23            | 9     | 12
sbenson1 | Benson    | Sally      | 222222   | 23            | 10    | 12
csmith2  | Smith     | Charlie    | 555555   | NULL          | NULL  | NULL (hasn't taken quiz yet)
dthomp1  | Thompson  | David      | 666666   | 23            | 9     | 12

到目前为止，我已经尝试了很多事情。第一个是基本的 LEFT JOIN。这会为每个学生生成一行，但由于我为“tests.assessment_id”指定了一个值，因此它不会为尚未参加测验的学生显示行。我希望它显示测验结果(如果存在)，如果学生没有参加测验，则显示一行包含 NULL 分数值。

SELECT
  users.last_name,
  users.first_name,
  tests.score,
  tests.maxScore
FROM users
  LEFT JOIN tests
    ON tests.username = users.username
WHERE tests.assessment_id = '23'
    AND users.section = 'S432-01'

以上生成:

username | last_name | first_name | schoolId | assessment_id | score | maxScore
------------------------------------------------------------------------------
jadams1  | Adams     | Joe        | 111111   | 23            | 9     | 12
sbenson1 | Benson    | Sally      | 222222   | 23            | 10    | 12
dthomp1  | Thompson  | David      | 666666   | 23            | 9     | 12

以下是更新的迭代。它为练习测验生成一个 NULL 行(没有分配 assessment_id)以及一个真实的行。在最终报告中，重要的是只为每个学生创建一行，但不要遗漏任何学生:

SELECT DISTINCT
  users.username,
  users.last_name,
  users.first_name,
  users.schoolId,
  if(tests.assessment_id ='23','23','') AS assessment,
  if(tests.assessment_id ='23',tests.score,'') AS score,
  if(tests.assessment_id ='23',tests.maxScore,'') AS maxScore
FROM `users`
  LEFT JOIN tests
    ON tests.username = users.username
WHERE users.section = 'S432-01'
    AND (tests.assessment_id = '23'
      OR tests.assessment_id IS NULL)
ORDER BY users.last_name,users.first_name,tests.assessment_id

以上生成:

username | last_name | first_name | schoolId  | assessment_id | score | maxScore
------------------------------------------------------------------------------------
jadams1  | Adams     | Joe        | 111111    | NULL          | NULL  | NULL
jadams1  | Adams     | Joe        | 111111    | 23            | 9     | 12
sbenson1 | Benson    | Sally      | 222222    | NULL          | NULL  | NULL
sbenson1 | Benson    | Sally      | 222222    | 23            | 10    | 12
csmith2  | Smith     | Charlie    | 555555    | NULL          | NULL. | NULL (hasn't taken quiz yet)
dthomp1  | Thompson  | David      | 666666    | NULL          | NULL. | NULL
dthomp1  | Thompson  | David      | 666666    | 23            | 9     | 12

关于如何在单个查询中完成这项工作有什么想法吗？我认为我遇到了性能问题，所以我希望让事情尽可能简单。

感谢您的帮助!-克里斯

Answer 1

这会起作用，

SELECT  users.last_name,
        users.first_name,
        tests.score,
        tests.maxScore
FROM    users
        LEFT JOIN tests
              ON tests.username = users.username AND 
                 tests.assessment_id = '23'
WHERE   users.section = 'S432-01'

您没有从 users 表中获取所有记录的原因是因为您过滤了等于 23 的 assessment_id，这会丢弃所有记录null 值(未参加评估的学生在此列中具有 null 值)。为了获得所有值，将 tests.assessment_id='23' 移到 ON 子句上。