使用返回超过 5 个结果的 TOP 5 访问 SQL? [英] Access SQL using TOP 5 returning more than 5 results?

问题描述

我正在使用以下语句

SELECT TOP 5 rootcause, COUNT(IIF(accountability="Team 1",1,0))
FROM  MOAQ
WHERE CDT=1
GROUP BY rootcause

MOAQ 是另一个查询，它从 4 个表中返回大约 20 个字段，没什么特别的.这按预期工作，我得到 5 个结果.

MOAQ is another query that returns about 20 fields from 4 tables, nothing special. This works as expected and I get 5 results.

如果我在条件字段上添加 ORDER BY 子句，尽管我开始得到 8 个结果.如果我按第一个字段排序没有问题.

If I add an ORDER BY clause on the conditional field though I start to get 8 results. If I sort by the first field there is no problem.

有人知道会发生什么吗?

Anyone know what might be going on?

编辑以澄清 - 此时我仅在 Access 2003 中进行测试，最终语句将通过 Excel 前端的 ADO 进行参数化查询.

Edit to clarify - I am only testing from within Access 2003 at this point, eventual statement will be parameterized query via ADO from Excel front end.

推荐答案

这是 Access 中 top 指令的已知效果，但它不是很出名...

This a known effect of the top directive in Access, but it's not very well known...

top 指令不返回顶部 n 项，因为很容易让人相信.相反，它返回至少 n 个由结果的排序决定的不同项目.

The top directive doesn't return the top n items, as one is easily led to believe. Instead it returns at least n distinct items determined by the ordering of the result.

在大多数情况下它是相同的，但在您的示例中，第 5 到第 8 项具有相同的排序值，所有这些都包含在内.它返回前五个项目，然后返回与第五个项目具有相同排序值的所有项目.

In most cases it's the same, but in your example where the 5th to 8th items have the same ordering value, all of them are included. It returns the first five items, but then also all items that have the same ordering value as the fifth item.

如果您不对表应用任何排序，则会考虑所有字段，因此如果结果中有唯一字段，则查询将始终返回五个项目.如果排序中包含唯一字段，当然也一样.

If you don't apply any ordering to the table, all fields are considered, so if you have a unique field in the result the query will always return five items. The same of course if the unique field is included in the ordering.

其他 SQL 方言的行为可能有所不同.例如，T-SQL (SQL Server) 中单独的 top 指令永远不会返回超过 n 项.但是，通过指定子句 with ties 和 order by 以及 top，可以观察到与 Access 中相同的行为.

Other dialects of SQL may behave differently. The top directive alone in T-SQL (SQL Server) for example never returns more than n items. However, by specifying the clauses with ties and order by along with top, one can observe the same behavior as in Access.

这篇关于使用返回超过 5 个结果的 TOP 5 访问 SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！