为什么在 where 子句中使用相关查询进行查询时，结果集中没有类似的 id

Question

我有一个表，其中包含列 id、名字、姓氏、创建(日期(。

我有一个如下表：

ID | Forename | Surname | Created
---------------------------------
 1 | Tom      | Smith   | 2008-01-01
 1 | Tom      | Windsor | 2008-02-01
 2 | Anne     | Thorn   | 2008-01-05
 2 | Anne     | Baker   | 2008-03-01
 3 | Bill     | Sykes   | 2008-01-20
Basically, I want this to return the most recent name for each ID, so it would return:
ID | Forename | Surname | Created
---------------------------------
 1 | Tom      | Windsor | 2008-02-01
 2 | Anne     | Baker   | 2008-03-01
 3 | Bill     | Sykes   | 2008-01-20

我通过此查询得到了所需的结果。

SELECT id, forename, surname, created
FROM name n
WHERE created = (SELECT MAX(created)
                              FROM name
                              GROUP BY id
                              HAVING id = n.id);

我

得到了我想要的结果，但我不明白为什么 ID 在结果集中没有重复。我对相关子查询的理解是它从外部查询表中获取一行并运行内部子查询。当 id 在外部查询中重复时，它不应该重复"id"吗？有人可以向我解释幕后到底发生了什么吗？

Answer 1

首先，您的子查询不需要GROUP BY。 它通常写成：

SELECT n.id, n.forename, n.surname, n.created
FROM name n
WHERE n.created = (SELECT MAX(n2.created)
                   FROM name n2
                   WHERE n2.id = n.id
                  );

您应该养成限定所有列引用的习惯，尤其是当您的查询具有多个表引用时。

我想你在问为什么这有效。 好吧，外部查询中的每一行都针对条件进行测试。 条件是："我的created是否与具有相同id的name表中所有行的最大created相同"。 在数据中，每id只有一行符合该条件，因此不会重复id。

Answer 2

您还可以考虑通过创建与最大(创建(列值来连接表：

SELECT n.id, n.forename, n.surname, n.created
  FROM name n
  RIGHT JOIN ( SELECT id, MAX(created) as created FROM name GROUP BY id ) t
    ON n.created = t.created;

或使用IN运算符：

SELECT id, forename, surname, created
  FROM name n
 WHERE ( id, created ) IN (SELECT id, MAX(created)
                             FROM name
                            GROUP BY id );

或在子查询中使用带有HAVING子句的EXISTS：

SELECT id, forename, surname, created
  FROM name n
 WHERE EXISTS (SELECT id
                 FROM name
                GROUP BY id
                HAVING MAX(created) = n.created 
                );

演示