MySQL 从多个表中选择和最近的记录

Question

我在选择查询时遇到问题，无法完全弄清楚如何解决。我有两个表:

TABLE_students

|--------|------------|--------|
| STU_ID |   EMAIL    |  NAME  |
|--------|------------|--------|
|   1    | a@e.com    | Bob    |
|   2    | b@e.com    | Joe    |
|   3    | c@e.com    | Tim    |
--------------------------------

TABLE_scores

|--------|------------|-------------|--------|
| SRE_ID |   STU_ID   |  DATE       |  SCORE |
|--------|------------|-------------|--------|
|  91    | 2          | 2018-04-03  |  78    |
|  92    | 2          | 2018-04-06  |  89    |
|  93    | 3          | 2018-04-03  |  67    |
|  94    | 3          | 2018-04-06  |  72    |
|  95    | 3          | 2018-04-07  |  81    |
----------------------------------------------

我正在尝试从两个表中选择数据，但有一些要求。我需要选择学生，即使他们在分数表中没有分数。我也只想要最新的成绩记录。

下面的查询只返回那些有分数的学生，它还会重复返回总共 5 行(因为有五个分数)。我想要的是查询返回三行(每个学生一行)和他们的最新分数值(如果他们没有分数，则返回 NULL):

SELECT students.NAME, scores.SCORE FROM TABLE_students as students, TABLE_scores AS scores WHERE students.STU_ID = scores.STU_ID;

我很难弄清楚如何拉所有学生，无论他们是否有分数，以及如果他们有分数，如何只拉出最新分数。

谢谢!

Answer 1

这是 greatest-n-per-group 的变体问题，这在 Stack Overflow 上很常见。

我会用几个连接来做到这一点:

SELECT s.NAME, c1.DATE, c1.SCORE
FROM students AS s
LEFT JOIN scores AS c1 ON c1.STU_ID = s.STU_ID
LEFT JOIN scores AS c2 ON c2.STU_ID = s.STU_ID 
  AND (c2.DATE > c1.DATE OR c2.DATE = c1.DATE AND c2.SRE_ID > c1.SRE_ID)
WHERE c2.STU_ID IS NULL;

如果 c2.STU_ID 为 null，则表示 LEFT JOIN 没有匹配到比 c1 中的行具有更大日期(或更大的 SRE_ID，如果是平局)的行。这意味着 c1 中的行必须是最新的，因为没有其他行更新。

P.S.:请学习 JOIN 语法，并避免“逗号式”连接。 JOIN 自 1992 年以来一直是标准。

P.P.S.:我从您的表名中删除了多余的“TABLE_”前缀。您不需要使用表名来提醒自己这是一个表! :-)