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

Question

为了简化这个问题，让我们假设这是一个学校数据库，我们有3个表。在这所学校，学生可以参加多门课程，有多节课，我们在数据库中存储了关于可用课程、每一门课程以及学生目前正在学习的课程和课程的信息。

表courses有2列，cs_id和cs_name。表lessons有3列:存储课程ID的ls_course、标识课程内课程的ls_number -a序列号和ls_name。最后一个表students存储该课程中的学生ID、st_id、课程ID和当前课程，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门课。在课程A中，她在第三节课，在B课，在第二课。我想要的是检索一个学生目前正在学习的课程的列表。但并不是说：

SELECT * FROM students WHERE st_id = 2;

这将返回过滤过的行，但我想检索课程和课程ID以及它们的名称。要获得课程名称列，我需要这样做：

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

但是，如何检索该课程ID和章节，以检查在“课程”表中是否匹配，有可能吗？我想要的结果是：

+-------+-----------+-----------+----------+--------------+
| 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)

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

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