在Access97 SQL中，如何返回行范围? [英] In Access97 SQL how do I return a range of rows?

问题描述

不幸的是，我有一个Access97数据库，并且正在通过ODBC查询它.

I have an Access97 database (unfortunately) and I am querying it over ODBC.

我想构造一个查询，该查询将返回从偏移量K开始的J个有序行.现在，对于相关操作，我正在使用子查询和TOP关键字，这很好，但我认为我不能将其用于我尝试执行的新操作.我想要这样做是因为我想向某些软件中添加某种形式的页面调度.

I want to construct a query that will return me J ordered rows starting at offset K. Right now for a related operation I am using a sub-query and the TOP keyword, this works fine but I don't think I can use it for this new operation I am trying to perform. I am wanting to do this because I want to add some form of paging to some software.

理想情况下，用户界面应该可以说给我50条记录，从记录150开始，按日期列排序.

Ideally the user interface should be able to say, give me 50 records, starting at record 150, sorted by the date column.

任何帮助将不胜感激.

推荐答案

您基本上是在尝试模仿Access中的ROW_NUMBER().不幸的是，Access并不是执行这种操作的好引擎.经过约一百次的记录，它使狗变慢了.

You're basically trying to mimic ROW_NUMBER() in Access. Unfortunately, Access is not a good engine for doing this kind of thing. It gets dog slow after a hundred or so records.

话虽如此，这是您可以做的:

With that said, here's what you can do:

SELECT t1.ID, t1.SomeText, t1.SomeDate,
(
  SELECT COUNT(*) + 1
  FROM Table1 AS t2
  WHERE t1.SomeText > t2.SomeText
) as RowNumber
FROM Table1 t1
ORDER BY t1.SomeText

这将产生此结果集(在我的特定表中):

That will produce this resultset (in my particular table):

ID | SomeText             | SomeDate  | RowNumber
10 | 4NGJN                | 1/20/2010 | 1
11 | ABH                  | 1/20/2010 | 2
2  | asldkfj              | 1/20/2010 | 3
1  | asldkfjaslgh         | 1/20/2010 | 4
7  | ewoiuhdkjnlbkjbn     | 1/20/2010 | 5
4  | oihkjldhnlkmdfn,mn   | 1/20/2010 | 6
3  | oihoih               | 1/20/2010 | 7
5  | qwwern               | 1/20/2010 | 8
8  | SKN                  | 1/20/2010 | 9
9  | WEOIN                | 1/20/2010 | 10
6  | wetrhn               | 1/20/2010 | 11
12 | XDNKJ                | 1/20/2010 | 12

我将在Access(SQL Server中的视图)中进行保存的查询，然后在您的应用程序中编写如下查询:

I would make a saved query in Access (a view in a SQL Server) and then write a query like this in your application:

SELECT ID, SomeText, SomeDate, RowNumber
FROM myView
WHERE RowNumber > 5
  AND RowNumber < 11

当然，"5"&您将从客户端应用程序传入"11"，并基于您所在的特定页面. (您可能会选择50和100之类的东西.)

Of course, "5" & "11" you would pass in from your client app, and would be based on the particular page you're on. (It would probably be 50 and 100 or something for you).

这篇关于在Access97 SQL中，如何返回行范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！