“选择顶部”、“左外连接”、“排序依据”给出额外的行
我在 .NET 中通过 OLE DB 运行以下 Access 2002 查询:
SELECT TOP 25
tblClient.ClientCode,
tblRegion.Region
FROM (tblClient LEFT OUTER JOIN
tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblRegion.Region
tblClient
中有 431 条记录的 RegionCode
设置为NULL
。
由于某种原因,上面的查询返回所有这 431 条记录,而不是前 25 条。
如果我将查询更改为 ORDER BY tblClient.Client
(客户端的名称),如下所示:
SELECT TOP 25
tblClient.ClientCode,
tblRegion.Region
FROM (tblClient LEFT OUTER JOIN
tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblClient.Client
我得到预期的结果结果集包含 25 条记录,显示区域名称和 NULL
值的混合。
为什么通过 LEFT OUTER JOIN
检索的字段进行排序时 TOP
子句不起作用?
编辑:可能的解决方案
当我也ORDER BY tblClient.Client
时,查询似乎有效:
SELECT TOP 25
tblClient.ClientCode,
tblRegion.Region
FROM (tblClient LEFT OUTER JOIN
tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblRegion.Region, tblClient.Client
因为我真的不介意是否按第二个字段,现在我将这样做。
I have the following Access 2002 query I'm running through OLE DB in .NET:
SELECT TOP 25
tblClient.ClientCode,
tblRegion.Region
FROM (tblClient LEFT OUTER JOIN
tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblRegion.Region
There are 431 records within tblClient
that have RegionCode
set to NULL
.
For some reason, the query above returns all these 431 records instead of the first 25.
If I change the query to ORDER BY tblClient.Client
(the name of the client) like so:
SELECT TOP 25
tblClient.ClientCode,
tblRegion.Region
FROM (tblClient LEFT OUTER JOIN
tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblClient.Client
I get the expected result set of 25 records, showing a mixture of region names and NULL
values.
Why is it that ordering by a field retrieved through a LEFT OUTER JOIN
will the TOP
clause not work?
EDIT: Possible solution
When I also ORDER BY tblClient.Client
the query appears to work:
SELECT TOP 25
tblClient.ClientCode,
tblRegion.Region
FROM (tblClient LEFT OUTER JOIN
tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblRegion.Region, tblClient.Client
Since I don't really mind if I sort by a second field, for now I will do this.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
您看到的行为并不是因为该字段是通过 LEFT OUTER JOIN 检索的,而是因为该字段为 NULL
SQL 中的 NULL 的行为与任何其他值不同
如果 a 和 b 都为 NULL,则 a = b 为 false。因此,在比较分组访问时,会将所有 NULL 视为不同的值。
在这种情况下,如果您想使用 TOP,您可以通过添加来排除 NULL 值
The behaviour you are seeing is not because the field is retrieved through a LEFT OUTER JOIN it is because the field is NULL
NULL in SQL does not behave like any other value
If a and b are both NULL then a = b is false. Thus when comparing for grouping access sees all the NULLs as different values
In this case if you want to use TOP you could exclude the NULL values by adding
我以前见过这种情况,这是因为如果 ORDER BY 中使用的第 25 列是唯一的,Access 只返回 25 行。如果重复发生,Access 还会返回绑定值,这意味着它可以在一个 ORDER BY 中返回超过 25 行,而在另一个 ORDER BY 中返回正好 25 行。
因此,如果 ORDER BY 的末尾命中 NULL,它将显示所有绑定 (NULL) 值。这种错误可能在较新版本的 Access 中已修复,但由于我在这台计算机上没有 Access,但您可以尝试:
选择前 5 个 {1,2,3,4,5,5,5,5} 升序并向下查看它是否适用于您的 Access 版本。
华泰
I've seen this before and then it was because Access only returned 25 rows if the 25th column used in the ORDER BY was unique. If it recurred Access also returned the tied values meaning that it can return more than 25 rows in one ORDER BY and exactly 25 in another.
So if the end of the ORDER BY hits NULL it would show all tied (NULL) values. This kind of bug is probably fixed in newer versions of Access, but asI don't have Access on this machine, but you could try:
select top 5 {1,2,3,4,5,5,5,5} ascending and descending to see if it applies to your version of Access.
HTH
我没有安装 Access 或您的架构,但这有效吗?
I don't have Access installed or your schema for that matter but does this work?
当我也
ORDER BY tblClient.Client
时,查询似乎有效:因为我并不介意是否按第二个字段排序,所以现在我将这样做。
我已按问题进行更新,以反映这是一个可能的解决方案。
When I also
ORDER BY tblClient.Client
the query appears to work:Since I don't really mind if I sort by a second field, for now I will do this.
I've updated by question to reflect this as a possible solution.