sql - OUTER JOIN 结果缺少行，没有 WHERE 子句(找到解决方法)

Question

底部更新。

我正在尝试做一个自外部连接，对于每条记录，返回它和发生在它之后的所有其他记录，如果它本身是最新记录，则返回 NULL。这是我的sql代码:

SELECT A.[CR#], A.REGIS_STATUSDATE, B.REGIS_STATUSDATE
FROM CR_ADMIN_REGIS_STATUS A LEFT OUTER JOIN CR_ADMIN_REGIS_STATUS B
ON A.[CR#]=B.[CR#] AND A.REGIS_STATUSDATE < B.REGIS_STATUSDATE

我的问题是，对于给定的 [CR#]，当 A.REGIS_STATUSDATE 是最大值(因此 B.REGIS_STATUSDATE 不能大于它)时，该行不包含在我的结果中。

例如，如果 CR_ADMIN_REGIS_STATUS 如下所示:

CR#   REGIS_STATUSDATE
1     5/1/12
1     5/2/12
1     5/3/12
2     5/1/12
2     5/2/12

我希望我的查询结果是

CR#   A.REGIS_STATUSDATE B.REGIS_STATUSDATE
1     5/1/12             5/2/12
1     5/1/12             5/3/12
1     5/2/12             5/3/12
1     5/3/12             NULL
2     5/1/12             5/2/12
2     5/2/12             NULL

相反，我得到了这个:

CR#   A.REGIS_STATUSDATE B.REGIS_STATUSDATE
1     5/1/12             5/2/12
1     5/1/12             5/3/12
1     5/2/12             5/3/12
2     5/1/12             5/2/12

鉴于我的查询是 LEFT OUTER JOIN，并且我没有 WHERE 子句，我希望原始表中的所有行都在结果中，但事实并非如此。我在这里缺少什么？

编辑:这是在 Access 2007 中

更新:我决定看看如果我将表 CR_ADMIN_REGIS_STATUS 的部分复制到一个单独的表中并针对它运行我的查询会发生什么。即使我刚刚直接将整个表复制到新表中(手动)，查询仍然有效!这只是在实际复制和粘贴时的情况，当我将 SELECT * INTO 另一个表时，问题仍然存在。
最终我发现如果我运行查询

SELECT *
FROM CR_ADMIN_REGIS_STATUS
UNION ALL SELECT TOP 1 * 
FROM CR_ADMIN_REGIS_STATUS;

而不是 CR_ADMIN_REGIS_STATUS 本身，我的查询返回了所需的结果。奇怪的。我也对一个从一开始就工作的类似表进行了类似的查询，所以这似乎是一个仅限于这个表的问题。

Answer 1

你没有错过任何东西。 如果发生这种情况，那就是一个错误。

MS-Access 使用的引擎有几个错误。我在具有“复杂”的连接中看到了类似的、无礼的行为 ON状况。查看另一个 SO 问题，其中 Access 给出了错误的结果:Why does my left join in Access have fewer rows than the left table?

您可以尝试使用 SQL-Server、Oracle、Postgres 甚至 MySQL 中的相同数据进行查询，您将获得正确的预期结果。

作为解决方法，您可以尝试使用 UNION 重写查询。 ，但人们永远无法确定其正确性:

SELECT A.[CR#], A.REGIS_STATUSDATE, B.REGIS_STATUSDATE
FROM CR_ADMIN_REGIS_STATUS A 
  INNER JOIN CR_ADMIN_REGIS_STATUS B
    ON  A.[CR#]=B.[CR#] 
    AND A.REGIS_STATUSDATE < B.REGIS_STATUSDATE

UNION ALL

SELECT A.[CR#], A.REGIS_STATUSDATE, NULL
FROM CR_ADMIN_REGIS_STATUS A 
WHERE NOT EXISTS
      ( SELECT *
        FROM CR_ADMIN_REGIS_STATUS B
        WHERE A.[CR#]=B.[CR#] 
          AND A.REGIS_STATUSDATE < B.REGIS_STATUSDATE
      ) ;