MS Access行号，指定索引 [英] MS Access row number, specify an index

问题描述

有没有办法在MS访问返回一个特定的索引之间的数据集？

所以让我说我的数据集是：

  rank | first_name | age 
 1 Max 23 
 2 Bob 40 
 3 Sid 25 
 4 Billy 18 
 5 Sally 19 
  

但我只想在'rank'2和4之间返回这些记录，所以我的结果集是Bob，Sid和Billy？但是，Rank不是表的一部分，并且应该在运行查询时生成。为什么我不使用自动生成的数字，因为如果记录被删除，这将是不一致的，如果我想要的结果，如果反向！

这显然很简单，我问的原因是因为我在一个产品目录，我正在寻找一个更有效的方式分页通过返回的数据集，所以如果我只从数据库返回1页的数据，这显然是要

感谢R。

解决方案

我认为你需要使用一个相关的子查询来计算飞行中的排名例如我猜这个排名是基于名称：

  SELECT T1.first_name，T1.age，
 b $ b SELECT COUNT（*）+ 1 
 From MyTable AS T2 
 WHERE T1.first_name> T2.first_name 
）AS rank 
 From MyTable AS T1;坏消息是Access数据引擎针对这种查询进行了较差的优化;对于这种类型的查询，在我的经验中，performace将开始显着降级超过几百行。 
 
 
 
如果无法维持房子数据库端的排名（例如高插入环境），请考虑在客户端执行翻页。例如，ADO经典记录集对象具有支持分页的属性（ PageCount ， PageSize ， 
 
 
 
与以前一样，你将会有一个对象（例如，AbsolutePage 以执行自己的时间，但我怀疑，当有，例如，10K行，你会发现它更快地承担所有行到一个ADO记录集，然后找到页面的开销（然后可能制作更小的ADO记录集该页面的行的价值），而不是执行相关的子查询，只获取页面的行数。
 
Is there a way in MS access to return a dataset between a specific index?

So lets say my dataset is:
rank | first_name | age
  1       Max       23
  2       Bob       40
  3       Sid       25
  4       Billy     18
  5       Sally     19
But I only want to return those records between 'rank' 2 and 4, so my results set is Bob, Sid and Billy?  However, Rank is not part of the table, and this should be generated when the query is run.  Why don't I use an autogenerated number, because if a record is deleted, this will be inconsistent, and what if I wanted the results in reverse!

This obviously very simple, and the reason I ask is because I am working on a product catalogue and I am looking for a more efficient way of paging through the returned dataset, so if I only return 1 page worth of data from the database this is obviously going to be quicker then return a complete set of 3000 records and then having to subselect from that set!

Thanks R.
解决方案
I think you need to use a correlated subquery to calculate the rank on the fly e.g. I'm guessing the rank is based on name:
SELECT T1.first_name, T1.age, 
       (
        SELECT COUNT(*) + 1
          FROM MyTable AS T2
         WHERE T1.first_name > T2.first_name
       ) AS rank
FROM MyTable AS T1;
The bad news is the Access data engine is poorly optimized for this kind of query; in my experience, performace will start to noticeably degrade beyond a few hundred rows. 

If it is not possible to maintain the rank on the db side of the house (e.g. high insertion environment) consider doing the paging on the client side. For example, an ADO classic recordset object has properties to support paging (PageCount, PageSize, AbsolutePage, etc), something for which DAO recordsets (being of an older vintage) have no support.

As always, you'll have to perform your own timings but I suspect that when there are, say, 10K rows you will find it faster to take on the overhead of fetching all the rows to an ADO recordset then finding the  page (then perhaps fabricate smaller ADO recordset consisting of just that page's worth of rows) than it is to perform a correlated subquery to only fetch the number of rows for the page.

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