如何在MS ACCESS中使用类似Oracle Rownum的功能 [英] How to use the same function like Oracle Rownum in MS ACCESS

问题描述

我遇到一个问题，我已经完成了一个可以通过检测滚动位置来加载数据的功能，该功能是使用SQL语句"Rownum"完成的，仅在Oracle中有效，而在ACCESS中则无效. >

我想查询数据并重新处理

ID  value
1   aa
3   bb

借助Rownum，我们可以做到这一点

NID ID value
1   1  aa
2   3  bb

如何使用Microsoft ACCESS编写SQL语句

解决方案

Access不支持该功能.如果您的ID字段是数字主键，则可以包含一个字段表达式，该表达式是当前ID值的ID< =的行数的计数.

SELECT
    DCount('*', 'YourTable', 'ID <= ' & y.ID) AS NID,
    y.ID,
    y.value
FROM YourTable AS y;

如果愿意，可以使用相关子查询代替DCount.

和ID实际上不一定是主键.如果具有唯一约束，则仍然适合此目的.

目标字段不一定非要是数字，但是文本数据类型可能更具挑战性.

I am encountering a problem, I had done a function that the data can be loaded by detecting scrolling position, the function was made with a SQL statement "Rownum", it only works in Oracle, but not in ACCESS.

I would like to query the data and resort it

ID  value
1   aa
3   bb

with Rownum we can do like this

NID ID value
1   1  aa
2   3  bb

how can I write a SQL statement with Microsoft ACCESS

解决方案

Access does not support that function. If your ID field is a numeric primary key, you can include a field expression which is the count of the number of rows with ID <= to the current ID value.

SELECT
    DCount('*', 'YourTable', 'ID <= ' & y.ID) AS NID,
    y.ID,
    y.value
FROM YourTable AS y;

You could use a correlated subquery instead of DCount if you prefer.

And ID does not actually have to be a primary key. If it has a unique constraint it is still suitable for this purpose.

And the targeted field does not absolutely have to be a number, but text data type can be more challenging.

这篇关于如何在MS ACCESS中使用类似Oracle Rownum的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！