如果不存在值，如何在左侧联接字段上返回null

Question

我有五张桌子:学生，科目，period_one，period_two和period_three。我正在执行一个左联接来从这五个表中选择值：

学生表(学生)

  student_id    | Name
  --------------|-------
  1             |John
  2             |Peter
  3             |Flomo 

科目表(科目)

  subject_id       |SubjectName
  -----------------|-------
  math101          |Mathematics
  eng201           |English
  lang303          |Language Arts

周期表(period_one)

  id|student_id |subject_id| score
  --------------|----------|-----
  1 |1          | math101  |99
  2 |2          | eng201   |88
  3 |3          | lang303  |77 

周期二表(period_two)

  id|student_id |subject_id| score
  --------------|----------|-----
  1 |1          | math101  |100
  2 |2          | eng201   |60
  3 |3          | lang303  |65 

周期三表(period_three)

  id|student_id |subject_id| score
  --------------|----------|-----
  1 |1          | math101  |71
  2 |2          | eng201   |51
  3 |3          | lang303  |71

下面是用于检索记录Query1的查询

SELECT period_one.student_id, period_one.subject_id, period_one.score, period_two.score,period_three.score

from period_one

LEFT JOIN period_two
ON period_one.subject_id = period_two.subject_id
AND period_one.student_id = period_two.student_id

LEFT JOIN period_three 
on period_one.subject_id = period_three.subject_id
AND period_one.student_id = period_three.student_id

WHERE period_one.student_id = 10      

上面的代码的问题是，如果我要查找的学生id不在第一个表(PeriodOne)中，则将左联接应用于查询返回null，即使该学生的记录位于其他表(periodTwo和periodThree)中。我查找了这个问题(left.asp)，并验证了这不是最好的方法。

，然后我做了一些修改，

因此，我更新了查询如下(Query2)：

SELECT students.student_id, period_one.score, period_one.subject_id, 
period_two.score, period_two.subject_id, period_three.score, 
period_three.subject_id
from students

LEFT JOIN period_one
ON students.student_id = period_one.student_id

LEFT JOIN period_two
ON students.student_id = period_two.student_id

LEFT JOIN period_three 
ON students.student_id = period_three.student_id

其中students.student_id =3或period_one.student_id =3或period_two.student_id =3或period_three.student_id =3

这是很好的，因为学生表是主表，所有的周期表都引用。这样，如果学生id不在period_one和period_two表中，而是在period_there表中，则返回该表的studentId、subjectId和score。

然后出现了另一个问题，

在我更新代码之前，我以我想要的方式显示记录，但并没有以我想要的方式获取/检索记录。这就是促使我更改查询的原因，因为我注意到了问题所在。

现在，基于我的第一个query(Query1)，我从select语句中的各个表中选择了subject_id。当我显示记录时，我将查询返回的subject_id传递给一个函数，该函数获取该id的主题名。我就是这样显示我的结果的：

​

​

如果学生id位于表“为from子句赋值”内，则此操作有效，否则它将不返回任何内容。这就是为什么我改变了我的代码。

但是，现在我已经将代码更改为(Query2)，我无法显示subject id及其名称，因为它不在subject表中。下面是我如何显示记录的要点：https://gist.github.com/nathansiafa/e9d22791800d4ba3a00e2b98de52baec

有什么办法能让我做得更好吗？将感谢建议以及反馈。谢谢!

Answer 1

从技术/编程的角度来看，您似乎有点过头了，而不是专注于想要拥有的数据的语义意义。

您真正想要的是 subjects 的列表，以及为特定学生的这些科目的评分。这里的学生是正交的，因为他们正在构建一个表格，但是学生不是那个表的一部分。

因此，第一步是选择我们想要的数据--受试者和他们的分数：

SELECT s.subject_id, s.subject_name, p1.score period_1_score, p2.score period_2_score, p3.score period_3_score 
FROM subject s
LEFT JOIN period_one p1 ON p1.subject_id = s.subject_id 
    AND p1.student_id = 10
LEFT JOIN period_two p2 ON p2.subject_id = s.subject_id 
    AND p2.student_id = 10
LEFT JOIN period_three p3 ON p3.subject_id = s.subject_id 
    AND p3.student_id = 10
ORDER BY s.subject_name;

这将给你你想要的表格数据-首先是所有的科目，然后是他们存在的分数。

现在，如果您坚持要在同一个查询中加载学生数据(我建议有一个单独的查询SELECT * FROM student WHERE student_id=10 )，那么您可以把它放在顶部：

SELECT s.subject_id, s.subject_name, p1.score period_1_score, p2.score period_2_score, p3.score period_3_score, st.name student_name
FROM subject s
LEFT JOIN period_one p1 ON p1.subject_id = s.subject_id 
    AND p1.student_id = 10
LEFT JOIN period_two p2 ON p2.subject_id = s.subject_id 
    AND p2.student_id = 10
LEFT JOIN period_three p3 ON p3.subject_id = s.subject_id 
    AND p3.student_id = 10
LEFT JOIN student st ON st.student_id = 10
ORDER BY s.subject_name;

Answer 2

对我来说，问题是数据不是以正常的形式出现，导致了问题的出现。由于主题和分数基本上是相同的结构，没有句号符号，我首先将所有三个表合并在一起，并在结果中创建一个“句号”列。

SELECT X.*, 1 as Period from Period_one X
UNION ALL 
SELECT Y.*, 2 as Period FROM PERIOD_TWO Y
UNION ALL
SELECT Z.*, 3 as Period FROM PERIOD_THREE Z

然后，我将其用作内联视图，女巫成为左联接的一部分，因此在第一个查询中，我们不会遇到缺少学生的问题。

SELECT Student_ID
     , max(Case when Period = 1 then B.Subject_ID end) as Period_one_Subject
     , max(case when Period = 1 then B.Score end) as Period_one_Score 
     , max(Case when Period = 2 then B.Subject_ID) end as Period_Two_Subject
     , max(case when Period = 2 then B.Score end as Period_two_Score 
     , max(Case when Period = 3 then B.Subject_ID end) as Period_Three_Subject
     , max(case when Period = 3 then B.Score end) as Period_Three_Score 
FROM STUDENTS S
LEFT JOIN (SELECT Y.*, 1 as Period from Period_one X
           UNION ALL 
           SELECT X.*, 2 as Period FROM PERIOD_TWO Y
           UNION ALL
           SELECT Z.*, 3 as Period FROM PERIOD_THREE Z) B
 on S.Student_ID = B.Student_ID
GROUP BY Student_ID

我们使用最大值和分组的学生来“旋转”的数据，使我们可以同时显示分数和主题。

如果我们需要实际的主题名称，这是一个简单的问题，就是在subject_ID键的基础上与您的subject表进行连接。

我假设一个学生只能出现在你的每个经期表中一次。