Access 2010限制查询结果 [英] Access 2010 Limit Query Results

问题描述

MS Access 2010中用于将查询结果限制为前1000个的语法是什么?

What's the syntax in MS Access 2010 for limiting the results of a query to the first 1,000?

我已经尝试过了

SELECT tblGL.[Cost Centre Code]
FROM tblGL
LIMIT 1000;

但是我得到了错误"FROM子句中的语法错误".

but I get the error 'Syntax error in FROM clause'.

我也尝试设置最大记录"属性，但它似乎无济于事-无论我在最大记录"字段中输入什么值，我仍然可以获得7,000多个结果.

I've also tried setting the Max Records property but it doesn't seem to do anything - I still get 7,000+ results regardless of what value I enter into the Max Records field.

我还希望有一个第二个查询，从第1,001个开始选择下一个25,000个.像这样:

I also want to have a 2nd query which selects the next 25,000, starting from the 1,001st. Something like:

SELECT tblGL.[Cost Centre Code]
FROM tblGL
LIMIT 1001, 25000;

推荐答案

MySQL的Access等效项是什么:LIMIT 1001，25000(即从第1,001个开始返回25,000个结果)?

What then is the Access equivalent of MySQL: LIMIT 1001, 25000 (ie return 25,000 results starting from the 1,001st)?

不幸的是，在MS Access中这不像在MySQL中那样简单.
在Access中，您需要使用嵌套的子查询.

Unfortunately, in MS Access this isn't as straightforward as in MySQL.
In Access, you need to work with nested subqueries.

这是我的答案，我在这里显示如何为C#中的分页构建正确的SQL字符串:
如何进行MS Access数据库分页+搜索?

Here' an answer of mine where I'm showing how to build the correct SQL string for paging in C#:
How to do MS Access database paging + search?

从该答案中获取SQL字符串并插入表名和列名将导致以下查询:

Taking the SQL string from that answer and inserting your table name and column names will result in this query:

select [Cost Centre Code] from tblGL
where [Cost Centre Code] in (
    select top 25000 sub.[Cost Centre Code]
    from (
        select top 26000 tab.[Cost Centre Code]
        from tblGL tab
        where 1=1
        order by tab.[Cost Centre Code]
    ) sub
    order by sub.[Cost Centre Code] desc
)
order by [Cost Centre Code]

这至少消除了对C#基本知识的需求，但是如果您不知道子查询的工作方式，恐怕您仍然会感到困惑:-)

This eliminates at least the need for basic C# knowledge, but I'm afraid you'll still be confused in case you don't know how subqueries work :-)

问题是:
Access没有内置方法可直接获取25000行，但跳过前1000行.
唯一可能的是获取前X行.

The problem is:
Access has no built-in way to directly get 25000 rows, but skip the first 1000.
The only thing that's possible is to get the first X rows.

所以我正在做这个(从内到外):

1. 加载前 26000 行
   (1000 + 25000，因为我们要跳过1000行然后加载25000)

从此数据集中，加载前25000行，但顺序降序.
这将有效地将行26000加载到1001 (该顺序，因为我们将行降序！)

From this dataset, load the first 25000 rows, but order descending.
This will effectively load row 26000 to 1001 (in that order, because we ordered the rows descending!)

要获得升序的行，只需再次从表中加载(然后升序！)，但只有步骤2中带有Cost Centre Codes的行.

To get the rows in ascending order, just load from the table again (and order ascending!), but only the rows with the Cost Centre Codes from step 2.

知道了吗?
是的，乍看之下似乎很吓人，但是只要您获得"子查询，实际上并不那么困难.

Got it?
Yes, it looks intimidating at first glance, but as soon as you "get" subqueries, it's actually not that difficult.

这篇关于Access 2010限制查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！