MySQL 连接，查询返回所有章节标题以及用户的等级（如果有）

Question

我有这样的表：

[users] [chapters] [exam_scores]
- id    - title    - fk_chapter
                   - fk_user
                   - grade

我需要一个查询来返回所有章节标题以及可用的用户等级。这是我的尝试：

select chapters.title as Title, exam_scores.grade as Results
from users, exam_scores left join chapters
on chapters.id = exam_scores.fk_chapter
where exam_scores.fk_user = users.id and users.id = 15;

如果有可用的成绩，则返回记录，但不返回其余章节。

Answer 1

您需要LEFT JOIN两次才能完成此操作。顺便说一句，我还建议避免使用“旧式”连接语法（FROM table1、table2、table3 ...），因为它太混乱了这些类型的连接。

select chapters.title as Title,
       exam_scores.grade as Results
from users
left join exam_scores
on exam_scores.fk_user = users.id
left join chapters
on chapters.id = exam_scores.fk_chapter
where users.id = 15;

Answer 2

SELECT chapters.title AS Title, user_scores.grade AS Results
FROM chapters
LEFT JOIN  
       ( SELECT fk_chapter, grade FROM exam_scores
         JOIN users ON exam_scores.fk_user = users.id
         WHERE users.id = 15 ) user_scores 
ON chapters.id = user_scores.fk_chapter

Answer 3

试试这个：

SELECT 
    chp. * , scr. * , usr. *
FROM 
    chapter AS chp
LEFT JOIN 
     scores AS scr ON scr.chapter_id = chp.id
LEFT JOIN 
     users AS usr ON usr.id = scr.user_id WHERE usr.id =15

Answer 4

是的，左连接两次：

select 
    chapters.title as Title
    , exam_scores.grade as Results
from 
    users 
    left outer join exam_scores 
        on (users.id = exam_scores.fk_user)
    left outer join chapters 
        on (exam_scores.fk_chapter = chapters.id)
where
    users.id = 15
;

Answer 5

由于您想要获取所有章节的列表，无论该章节是否有用户评分。因此，您需要从章节表中进行 SELECT。然后，由于您想要每个章节的结果，因此需要 LEFT JOIN exam_scores 表，以便您将获得有分数的行的记录，以及没有分数的行的记录。使用自然连接（JOIN 或 FROM table, table2, table3）时，仅返回左值和右值匹配的记录。

因此，您的查询应如下所示：

SELECT c.title Title, s.grade Results
FROM chapters c
LEFT JOIN exam_scores s ON s.fk_chapter = c.id
WHERE (s.fk_user = 15 OR s.fk_user IS NULL)

请注意，您甚至不需要在此处包含 users 表，因为您没有访问它的任何信息。您的查询是加入 users 表，然后对其 id 键执行 WHERE 子句。这不是必需的，除非您从用户表中获取数据。事实上，如果您确实需要用户表中的数据，则可以将其左连接到 exam_scores 表上，如下所示：

SELECT c.title Title, s.grade Results, u.*
FROM chapters c
LEFT JOIN exam_scores s ON s.fk_chapter = c.id
LEFT JOIN users u ON u.id = s.fk_user
WHERE (s.fk_user = 15 OR s.fk_user IS NULL)

希望这对您将来应该如何更好地构建查询和连接有所帮助并提供一些启示。

编辑：我编辑了我的答案以回应您的评论。这是完全有道理的，为什么您不会在没有附加成绩的情况下收到回复，原因如下：

当您将两个表连接在一起时，它们将通过用户 ID 连接起来。没关系，因为当用户 ID 未映射到章节表时，您会得到用户 ID 和等级的 NULL 值。但是，由于在 WHERE 子句中，我们正在过滤 ID 为 15 的用户，因此它会过滤掉用户列的所有 NULL 值。因此，为了解决这个问题，您可以在 WHERE 子句中添加一个 OR 语句，表示用户 ID 为 NULL 是可以的。