MS Access LIMIT X，Y [英] MS Access LIMIT X, Y

问题描述

是否可以模拟以下MySQL查询:

Is it possible to emulate the following MySQL query:

SELECT * FROM `tbl` ORDER BY `date` DESC LIMIT X, 10

(X是一个参数)

在MS Access中?

in MS Access?

推荐答案

虽然 Access/JET TOP关键字不直接提供OFFSET功能，但我们可以巧妙地组合使用TOP，子查询和派生表"以获得相同的结果.

While the Access/JET TOP keyword does not directly provide an OFFSET capability, we can use a clever combination of TOP, a subquery, and a "derived table" to obtain the same result.

以下是在ORDER BY 名称和 Id 中的 Person 表中从偏移量20开始的10行的示例.

Here is an example for getting the 10 rows starting from offset 20 in a Person table in ORDER BY Name and Id...

SELECT Person.*
FROM Person
WHERE Person.Id In 
      (
        SELECT TOP 10 A.Id
        FROM [
               SELECT TOP 30 Person.Name, Person.Id
               FROM Person
               ORDER BY Person.Name, Person.Id
             ]. AS A
        ORDER BY A.Name DESC, A.Id DESC
      )
ORDER BY Person.Name, Person.Id;

本质上，我们查询前30名，颠倒顺序，查询前10名，然后从表中选择匹配的行，再次按正向顺序进行排序.假设 Id 是PRIMARY KEY，并且 Name 上有一个索引，这应该是相当有效的.为了获得最佳性能，可能需要在 Name ， Id (而不是仅在 Name 上创建一个)的特定覆盖索引，但是我认为索引隐式地覆盖了PRIMARY KEY.

Essentially, we query the top 30, reverse the order, query the top 10, and then select the rows from the table that match, sorting in forward order again. This should be fairly efficient, assuming the Id is the PRIMARY KEY, and there is an index on Name. It might be that a specific covering index on Name, Id (rather than one on just Name) would be needed for best performance, but I think that indexes implicitly cover the PRIMARY KEY.

这篇关于MS Access LIMIT X，Y的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！