如何在 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
?
对此的任何帮助将不胜感激;我在谷歌上的搜索产生了无用的结果:(
Any help would be appreciated with this; my searches on google have yielded unhelpful results :(
推荐答案
如果您希望在 MS Access 中应用分页,请使用此
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.
Where 15 is the StartPos + PageSize, and 5 is the 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屋!