如何在SQL中为MS Access实现分页? [英] How do I implement pagination in SQL for MS Access?

问题描述

我正在通过OdbcConnection类使用ASP.NET访问Microsoft Access 2002数据库(MDB)，尽管运行非常缓慢，但效果很好.

I'm accessing a Microsoft Access 2002 database (MDB) using ASP.NET through the OdbcConnection class, which works quite well albeit very slowly.

我的问题是关于如何在SQL中实现对数据库查询的分页，正如我所知，我可以将TOP子句实现为:

My question is about how to implement pagination in SQL for queries to this database, as I know I can implement the TOP clause as:

SELECT TOP 15 *
FROM table

但是我找不到像使用ROWNUMBER的SQL Server那样将其限制为偏移量的方法.我最大的尝试是:

but I am unable to find a way to limit this to an offset as can be done with SQL Server using ROWNUMBER. My best attempt was:

SELECT ClientCode,
    (SELECT COUNT(c2.ClientCode)
        FROM tblClient AS c2
        WHERE c2.ClientCode <= c1.ClientCode)
    AS rownumber
FROM tblClient AS c1
WHERE rownumber BETWEEN 0 AND 15

，失败的原因是:

错误源:Microsoft JET数据库引擎

Error Source: Microsoft JET Database Engine

错误消息:没有为一个或多个必需参数提供值.

Error Message: No value given for one or more required parameters.

我无法解决此错误，但我假设它与确定rownumber的子查询有关?

I can't work out this error, but I'm assuming it has something to do with the sub-query that determines a rownumber?

任何帮助将不胜感激；我在Google上的搜索产生了无用的结果:(

Any help would be appreciated with this; my searches on google have yielded unhelpful results :(

推荐答案

如果您希望在MS Acces中应用分页，请使用此

If you wish to apply paging in MS Acces use this

SELECT *
FROM (
    SELECT Top 5 sub.ClientCode
    FROM (
        SELECT TOP 15 tblClient.ClientCode
        FROM tblClient
        ORDER BY tblClient.ClientCode
    ) sub
   ORDER BY sub.ClientCode DESC
) subOrdered
ORDER BY subOrdered.ClientCode

其中15是 StartPos + PageSize ，5是 PageSize .

编辑评论:

您收到的错误是因为您试图引用在同一查询级别中分配的列名，即 rownumber .如果您要将查询更改为:

The error you are receiving, is because you are trying to reference a column name assign in the same level of the query, namely rownumber. If you were to change your query to:

SELECT *
FROM (
    SELECT ClientCode,
           (SELECT COUNT(c2.ClientCode)
            FROM tblClient AS c2
            WHERE c2.ClientCode <= c1.ClientCode) AS rownumber                
    FROM tblClient AS c1
)
WHERE rownumber BETWEEN 0 AND 15

它不应该给您一个错误，但是我不认为这是您想要的分页结果.

It should not give you an error, but i dont think that this is the paging result you want.

这篇关于如何在SQL中为MS Access实现分页?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！