OUTER JOIN结果缺少行，没有WHERE子句(找到解决方法) [英] OUTER JOIN result is missing rows, no WHERE clause (Workaround found)

问题描述

在底部更新.

我正在尝试进行一个自我外部联接，对于每个记录，它都会返回它，并且所有其他记录的出现都晚于它，如果它本身是最新记录，则返回NULL.这是我的sql代码:

I am trying to do a self outer join that, for each record, returns it and all other records occuring later than it, or NULL if it itself is the latest record. This is my sql code:

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不能大于它)，则该行不包含在我的结果中.

My issue is this is that when, for a given [CR#], A.REGIS_STATUSDATE is a maximum (and therefore the B.REGIS_STATUSDATE cannot be greater than it), that row is not included in my result.

例如，如果CR_ADMIN_REGIS_STATUS看起来像这样:

For example, if CR_ADMIN_REGIS_STATUS looks like this:

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子句，我希望原始表中的所有行都在结果中，但事实并非如此.我在这里想念什么?

Given that my query is a LEFT OUTER JOIN, and I have no WHERE clause, I expect all rows from my original table to be in the result, but this is not the case. What am I missing here?

这在Access 2007中

This is in Access 2007

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

Update: I decided to see what would happen if I copied sections of the table CR_ADMIN_REGIS_STATUS into a separate table and ran my query against that. Even when I had just straight up copied the entire table into the new one (manually) the query worked! This was only the case when actually copying and pasting though, when I would SELECT * INTO another table the problem would persist.
Eventually I discovered that if I ran the query against

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

我的查询不是CR_ADMIN_REGIS_STATUS本身，而是返回了所需的结果.奇怪的.我也从一开始就对类似的表进行了类似的查询，因此看来这是仅限于该表的问题.

rather than CR_ADMIN_REGIS_STATUS itself my query returned the desired result. Weird. I also had a similar query against a similar table which worked from the start, so it seems this was an issue limited to this one table.

推荐答案

您不会丢失任何东西. 如果发生这种情况，那就是一个错误.

You are not missing anything. If this happens, it's a bug.

MS-Access使用的引擎存在多个错误.我在具有复杂" ON条件的联接中看到了类似的，不明确的行为.请参阅另一个SO问题，其中Access会给出错误的结果:

The engine used by MS-Access has several bugs. I've seen similar, inavlid behaviour in joins that had "complex" ON conditions. See another SO question where Access gives buggy results: Why does my left join in Access have fewer rows than the left table?

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

You can try the query with identical data in SQL-Server, Oracle, Postgres, even MySQL and you will get the correct, expected results.

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

As a workaround, you can try rewriting the query with a UNION, but one can never be sure about the correctness:

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
      ) ;

这篇关于OUTER JOIN结果缺少行，没有WHERE子句(找到解决方法)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！