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给出错误的结果：为什么我在Access中的左联接的行数少于左表？

您可以使用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
      ) ;