MS 访问限制 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 Name 和 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 访问限制 X、Y的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！