获取具有最大日期和空日期的不同 ID 记录

Question

这是我使用的左连接表1和表2。虽然看起来它可以在一个表中。表 1 类似于主列表，其中 id 不能重复所以在表 2 中，我添加了 id 可以重复的名称和日期

+----+---------+----------------+
| id | Date    | Name           |
+----+---------+----------------+
| 1  | 5/25/17 | Monica Coleman |
| 1  | 5/26/17 | Kelsi Mines    |
| 2  |         |                |
| 3  | 5/9/17  | Vanessa Heart  |
| 4  | 5/8/17  | Ryan Evans     |
| 4  | 5/10/17 | Sharpay Evans  |
| 5  |         |                |
| 6  |         |                |
| 7  | 5/6/17  | Troy Bolton    |
+----+---------+----------------+

如果我使用此代码...

SELECT P.id, R.DateAdded, R.Name FROM Table1 AS P LEFT JOIN Table2 AS R ON P.id=R.id WHERE ((([R.DateAdded])=(SELECT MAX(DateAdded) FROM R WHERE InventoryID = R.InventoryID)));

。然后这些结果显示...

+----+---------+----------------+
| id | Date    | Name           |
+----+---------+----------------+
| 1  | 5/26/17 | Kelsi Mines    |
| 3  | 5/9/17  | Vanessa Heart  |
| 4  | 5/10/17 | Sharpay Evans  |
| 7  | 5/6/17  | Troy Bolton    |
+----+---------+----------------+

但我希望声明返回的是...

+----+---------+----------------+
| id | Date    | Name           |
+----+---------+----------------+
| 1  | 5/26/17 | Kelsi Mines    |
| 2  |         |                |
| 3  | 5/9/17  | Vanessa Heart  |
| 4  | 5/10/17 | Sharpay Evans  |
| 5  |         |                |
| 6  |         |                |
| 7  | 5/6/17  | Troy Bolton    |
+----+---------+----------------+

Answer 1

好的，所以我得到了答案。 我使用 是空的

SELECT P.id, R.DateAdded, R.Name FROM Table1 AS P LEFT JOIN Table2 AS R ON P.id=R.id WHERE ((([R.DateAdded])=(SELECT MAX(DateAdded) FROM R WHERE InventoryID = R.InventoryID))) OR R.DateAdded IS NULL;

Answer 2

在大多数数据库中，您会将条件添加到 ON 子句中(或使用 ROW_NUMBER() . MS Access 都不支持这两种方法。 因此，请使用子查询：

SELECT P.id, R.DateAdded, R.Name
FROM Table1 AS P LEFT JOIN
     (SELECT R.*
      FROM Table2 AS R
      WHERE R.DateAdded = (SELECT MAX(R2.DateAdded) FROM Table2 R2 WHERE R.InventoryID = R2.InventoryID)
     ) as R
     ON P.id = R.id