mysql - 立即从关系数据库中检索列

Question

为了简化问题，我们假设这是一个学校数据库，并且有 3 个表。在这所学校，学生可以参加多个类(class)，这些类(class)有多个类(class)，我们在数据库中存储有关可用类(class)、每个类(class)的课时以及学生当前正在学习哪些类(class)和类(class)的信息。

表courses有2列，cs_id和cs_name。表 lessons 有 3 列，ls_course 存储类(class)所在类(class)的 ID，ls_number — 标识类(class)的序列号类(class) - 和 ls_name。最后一个表 students 存储学生 ID、st_id、类(class) ID 以及该类(class)中的当前类(class)、st_course 和 st_lesson.一些示例数据:

courses
+-------+----------+
| cs_id | cs_name  |
+-------+----------+
|     1 | Course A |
|     2 | Course B |
+-------+----------+

lessons
+-----------+-----------+--------------+
| ls_course | ls_number | ls_name      |
+-----------+-----------+--------------+
|         1 |         1 | Lesson One   |
|         1 |         2 | Lesson Two   |
|         1 |         3 | Lesson Three |
|         2 |         1 | Lesson One   |
|         2 |         2 | Lesson Two   |
|         2 |         3 | Lesson Three |
+-----------+-----------+--------------+

students
+-------+-----------+-----------+
| st_id | st_course | st_lesson |
+-------+-----------+-----------+
|     1 |         1 |         2 |
|     2 |         1 |         3 |
|     2 |         2 |         2 |
+-------+-----------+-----------+

如您所见，我们当前有 2 名学生，ID 2 的学生当前同时选修 2 门类(class)。在类(class) A 中，她在第三课，在类(class) B 中，她在第二课。我想要的是检索一名学生当前正在学习的类(class)的列表。但不是:

SELECT * FROM students WHERE st_id = 2;

这会返回过滤后的行，但我想检索它们的类(class)和类(class) ID和名称。要获取类(class)名称列，我会这样做:

SELECT s.*, c.cs_name
FROM students s, courses c
WHERE s.st_id = 2 AND s.st_course = c.cs_id

结果是:

+-------+-----------+-----------+----------+
| st_id | st_course | st_lesson | cs_name  |
+-------+-----------+-----------+----------+
|     2 |         1 |         3 | Course A |
|     2 |         2 |         2 | Course B |
+-------+-----------+-----------+----------+

我能到达的距离是:

SELECT s.*, c.cs_name, l.ls_name
FROM students s, courses c, lessons l
WHERE 
    s.st_id = 2            AND
    s.st_course = c.cs_id  AND
    s.st_lesson = l.ls_number

但是我如何检索类(class) ID 和章节以检查类(class)表中是否匹配，这可能吗？我想要的结果是:

+-------+-----------+-----------+----------+--------------+
| st_id | st_course | st_lesson | cs_name  | ls_name      |
+-------+-----------+-----------+----------+--------------+
|     2 |         1 |         3 | Course A | Lesson Three |
|     2 |         2 |         2 | Course B | Lesson Two   |
+-------+-----------+-----------+----------+--------------+

Answer 1

此查询错误:

 SELECT s.*, c.cs_name, l.ls_name
    FROM students s, courses c, lessons l
    WHERE 
        s.st_id = 2            AND
        s.st_course = c.cs_id  AND
        s.st_lesson = l.ls_number

您还需要将 st_course 与 ls_course 匹配

 SELECT s.*, c.cs_name, l.ls_name
    FROM students s, courses c, lessons l
    WHERE 
        s.st_id = 2            AND
        s.st_course = c.cs_id  AND
        (s.st_lesson = l.ls_number AND st.st_course=l.ls_course)

我还建议使用当前的 JOIN 语法，即

SELECT s.*, c.cs_name, l.ls_name
FROM students s 
JOIN courses c ON c.cs_id=s.st_course
JOIN lessons l ON (s.st_lesson=l.ls_number AND st.st_course=l.ls_course)
WHERE s.st_id = 2