使用TOP 5访问SQL返回超过5个结果? [英] 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.

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