如何查看ado.locate（Delphi）结果中的最后一条记录 [英] How to go to the last record in results of ado.locate (Delphi)

问题描述

我通过此代码找到了一些记录：

I located some records by this code:

ADOQuery1.Locate('field1',ADOQuery2.FieldByName('field2').Value,[])  

如何转到这些记录的最后一条？

How to go to the last one of these records?

推荐答案

您有很多选择。最好的选择取决于您在问题中没有提到的很多注意事项。我将提供非常简短的选项的概述，以避免这种情况变得过于广泛。您可以自行选择并确定详细信息。如果遇到困难，可以提出一个更具体的新问题。

You have a number of options. The best depends on a whole lot of considerations you haven't mentioned in your question. I'll provide a very brief overview of the options to avoid this becoming "too broad". It'll be up to you to make your choice and figure out the details. If you get stuck, you can ask a new, more specific question.

如果您的数据集是，则涉及 Locate 的解决方案仅 是可行的> 按您搜索的相同字段排序 。

A solution involving Locate is only feasible if your dataset is sorted by the same field you're searching on.

很显然，您的搜索值不是唯一关键字。因此，我猜测您正在尝试在按其他 unique 字段排序的数据中找到与搜索关键字匹配的最后一行。 （否则， last 的概念将毫无意义。）

Clearly your Search Value is not a unique key. So I'm guessing that you're trying to find the last row matching Search Key in data sorted by some other unique field. (Otherwise the concept of last is meaningless.)

因此，这很可能不适合您；除非您的数据按搜索字段的复合键和唯一键排序。

So it's highly probable this is not appropriate for you; unless your data is ordered by a composite key of your search field followed by a unique key.

方法很简单：向前导航直到找到搜索值不匹配的行，然后回退1行。

The approach is simple: navigate forwards until you find a row where the search value doesn't match, then backtrack by 1 row.

if not DataSet.Locate(SearchField, SearchValue, []) then
  { handle not found case as desired }
else
begin
  while (not DataSet.Eof) and (DataSet.FieldByName(SearchField).Value = SearchValue) do
    DataSet.Next;

  { Watch out for case that last row in dataset matches search value }
  if (DataSet.FieldByName(SearchField).Value <> SearchValue) then
    DataSet.Prior;
end;

实施您自己的搜索

这是直截了当，并且将始终有效。但这是低效的，具有O（n）复杂度。因此，不建议使用大型数据集。

Implement your own search

This is straight-forward and will always work. But it is inefficient, having O(n) complexity. So not advised for large datasets.

DataSet.Last;
while (not DataSet.Bof) and (DataSet.FieldByName(SearchField).Value <> SearchValue) do
  DataSet.Prior;

注意： 对于 Locate ，建议增强此方法以处理根本找不到匹配项的情况。在那种情况下，活动记录不应作为搜索的副作用而被无意更改。

NOTE: In order to mirror behaviour of Locate it would be advisable to enhance this method to deal with the case where a match is not found at all. In that case the active record should not be inadvertently changed as a side-effect of the search.

显然，此解决方案取决于筛选数据集是否适合其余代码。但这是一个非常简单的选项，并且取决于超出此答案范围的因素，它可能比上一个选项更具性能。

Obviously this solution depends on whether filtering the dataset is appropriate to the rest of your code. But it is a fairly simple option, and depending factors beyond the scope of this answer, it can be more performant than the previous option.

DataSet.Filtered := False;
{ The next line may be a little tricky.
  Ensure the filter string is appropriate for the data-types involved. }
DataSet.Filter := '<string of the form SearchField = SearchValue>';
DataSet.Filtered := True;
DataSet.Last;

请参见过滤器属性。

注意： 最好采取预防措施，以免重复设置过滤器。

NOTE: It may be advisable to take precaution against setting the filter redundantly.

包含此选项是因为您的问题代码指示SearchValue来自另一个数据集的活动记录。您正在使用ADO，因此可以使用此选项。

This option is included because your question code indicates the SearchValue comes from the active record of another dataset. You're using ADO, so this option is available to you.

DataSet.MasterSource := <Appropriate DataSource>;
DataSet.MasterFields := SearchField;
DataSet.Last;

请参阅主从关系和 ADO MasterFields 。

最后，值得考虑使用存储过程从数据库直接获取所需的信息。优点是服务器可以利用可用索引，并有可能提供性能最高的选项。再次强调，这很大程度上取决于应用程序的细节。

Finally, it's worth considering using a stored procedure to get the information you need directly from the database. The advantage is that the server can leverage available indexes and have the potential to provide the most performant option. Again though, a lot depends on the particulars of your application.

以下几行查询可以构成存储过程的基础。

A query along the following lines can form the basis of your stored procedure.

select  MAX(UniqueField) as RowKey
from    Table
where   SearchField = SearchValue

然后调用存储过程，并使用其结果查找所需的行。

Then call your stored procedure, and use its result to find the desired row.

DataSet.Locate(UniqueField, RowKey, []);

注意： 如果不存在带有SearchValue的行，则考虑存储过程返回 NULL 。

以上所有代码非常简短，仅用于说明目的。在许多情况下，要实现可靠的实施，还需要附加代码。

例如可能需要 DisableControls 并再次启用它们。

All the above code is extremely brief and for illustrative purposes only. In many cases additional code is required for a robust implementation.
E.g. It might be necessary to DisableControls and enable them again.

注意： 上面的内容很重要，要知道数据集中数据的实际排序。不考虑这一点会导致错误的行为。 如果您的数据集未按UniqueKey排序，即使最后一个选项的性能也可能比预期的差。

NOTE: It's very important with the above to be aware of the actual ordering of the data in your datasets. Failure to take this into account can lead to incorrect behaviour. Even the last option may exhibit worse than expected performance if your dataset is not sorted by UniqueKey.

这篇关于如何查看ado.locate（Delphi）结果中的最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！