mysql - 连接表格以显示正在进行和已完成的类(class)

Question

我正在制作一个类似大学的数据库，并且想知道如何加入我的注册和类(class)表，以显示所有学生所修读和目前注册的所有类(class)。以下是部分报名表:

+--------------+------------+-------------------+---------------+-------------+
| EnrollmentID | EnrolledOn | Student_StudentID | Class_ClassID | Status      |
+--------------+------------+-------------------+---------------+-------------+
|            1 | 08-23-2013 |                 1 |             1 | In Progress |
|            2 | 08-23-2013 |                 1 |             2 | In Progress |
|            3 | 08-23-2013 |                 1 |             3 | In Progress |
|            4 | 08-23-2013 |                 1 |             4 | In Progress |
|            5 | 08-23-2013 |                 1 |             5 | In Progress |
|            6 | 08-23-2013 |                 1 |             6 | In Progress |
|            7 | 08-23-2013 |                 1 |            22 | In Progress |
|            8 | 08-23-2013 |                 1 |            23 | In Progress |
|            9 | 08-23-2013 |                 1 |            36 | In Progress |
|           10 | 08-23-2013 |                 1 |            37 | In Progress |
|           11 | 08-23-2013 |                 2 |             7 | In Progress |
|           12 | 08-23-2013 |                 2 |             8 | In Progress |
|           13 | 08-23-2013 |                 2 |             9 | In Progress |
|           14 | 08-23-2013 |                 2 |            10 | In Progress |
|           15 | 08-23-2013 |                 2 |            11 | In Progress |

这是所修类(class)的部分表格:

+-------------------+-----------------+-----------+--------+-----------+
| Student_StudentID | Course_CourseID | Pass/Fail | Credit | Status    |
+-------------------+-----------------+-----------+--------+-----------+
|                 2 |               1 |         1 |      1 | Completed |
|                 2 |               2 |         1 |      1 | Completed |
|                 2 |               3 |         1 |      1 | Completed |
|                 2 |               4 |         1 |      1 | Completed |
|                 2 |               5 |         1 |      1 | Completed |
|                 2 |               6 |         1 |      1 | Completed |
|                 2 |              22 |         1 |      1 | Completed |
|                 2 |              23 |         1 |      1 | Completed |
|                 2 |              24 |         1 |      1 | Completed |
|                 2 |              25 |         1 |      1 | Completed |
|                 3 |               1 |         1 |      1 | Completed |
|                 3 |               2 |         1 |      1 | Completed |
|                 3 |               3 |         1 |      1 | Completed |
|                 3 |               4 |         1 |      1 | Completed |
|                 3 |               5 |         1 |      1 | Completed |
|                 3 |               6 |         1 |      1 | Completed |

正如您所看到的，一些学生(例如学生 1)由于是一年级学生，因此没有修读任何类(class)，仅注册了类(class)。我想要一张表，可以将两个表中的所有学生 ID 放入一个表或 View 中，并列出他们的类(class)状态是否为“进行中”或他们是否已完成类(class)。

关于如何加入这两个表有什么建议吗？

Answer 1

您可以按学生对两个表进行分组，然后使用联合查询将所有学生 ID 添加到一个结果中。

将此查询用作子查询，并对两个表进行左外连接，并获取与所需学生相关的信息。