连接两个 SQL 表时切换一列

Question

场景是，如果我有一张学生表，他们有 4 个班级，他们只知道房间号。还有两张表，一张是老师姓名和房间号，另一张是老师姓名以及他们所教授的科目。现在学生想知道谁是他们的数学老师，他们只想要学生姓名和数学老师姓名。

这是我正在从事的一个凭证项目的虚构场景。我已经让它在很多情况下都能工作，但速度非常慢。用于创建新列的 case 条件没有任何减慢速度，并且我使用相同的 case 条件左连接表，因为我们不知道将学生表与教师表关联到哪一列。左连接中的情况条件似乎是造成麻烦的原因，我还可以使用其他任何方法来获得相同的结果而不造成延迟吗？

Answer 1

我认为您遇到这么多问题的原因是您的架构不太好。特别是学生表的架构，其中每个课程/房间号都有一列。

我首先使用子查询来纠正这个问题，例如：

SELECT `Student Name`, 1 as Period, `1st Room#` as Room FROM students
UNION ALL
SELECT `Student Name`, 2 as Period, `2nd Room#` as Room FROM students
UNION ALL 
SELECT `Student Name`, 3 as Period, `3rd Room#` as Room FROM students
UNION ALL 
SELECT `Student Name`, 4 as Period, `4th Room#` as Room FROM students

像这样的“Unpivoting”将为您提供一个漂亮干净的 Student Name |期间 | Room 架构将使解决这个问题变得更容易。

SELECT `Student Name`, Subject.`Teacher Name`
FROM 
    (
      SELECT `Student Name`, 1 as Period, `1st Room#` as Room FROM students
      UNION ALL
      SELECT `Student Name`, 2 as Period, `2nd Room#` as Room FROM students
      UNION ALL 
      SELECT `Student Name`, 3 as Period, `3rd Room#` as Room FROM students
      UNION ALL 
      SELECT `Student Name`, 4 as Period, `4th Room#` as Room FROM students
    ) students
    INNER JOIN teacherRoom
       ON students.Room = teacherRoom.`Room Number`
    INNER JOIN teacherSubject
       ON teacherRoom.`Teacher Name` = teacherSubject.Subject
WHERE teacherSubject.Subject = 'Math';