如何在内部处理不在tDBGrid上显示结果的过滤后的tDataSet记录

Question

在下面的tFDMemTable中，我尝试对ID字段以字母A开头的记录求和。A1，A2，结果应该是4。

type
  TForm1 = class(TForm)
    FDMemTable1: TFDMemTable;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Button1: TButton;
    Button2: TButton;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  end;

procedure TForm1.FormCreate(Sender: TObject);
var
  _FieldDef: TFieldDef;
begin
  _FieldDef := FDMemTable1.FieldDefs.AddFieldDef;

  _FieldDef.Name := 'ID';
  _FieldDef.DataType := ftString;
  _FieldDef.Size := 5;

  _FieldDef := FDMemTable1.FieldDefs.AddFieldDef;

  _FieldDef.Name :='value';
  _FieldDef.DataType := ftInteger;

  FDMemTable1.CreateDataSet;

  FDMemTable1.Append;

  FDMemTable1.FieldValues['ID'] := 'A1';
  FDMemTable1.FieldValues['value'] := 1;

  FDMemTable1.Append;

  FDMemTable1.FieldValues['ID'] := 'B1';
  FDMemTable1.FieldValues['value'] := 2;

  FDMemTable1.Append;

  FDMemTable1.FieldValues['ID'] := 'A2';
  FDMemTable1.FieldValues['value'] := 3;

  FDMemTable1.Append;

  FDMemTable1.FieldValues['ID'] := 'B2';
  FDMemTable1.FieldValues['value'] := 4;
end;

我编写了以下代码，但它将tDBGrid更改为已过滤。我想要的只是一个内部过程，tDBGrid应该保持不变。

procedure TForm1.Button1Click(Sender: TObject);
var
  _ValueSum: Integer;
  i: Integer;
begin
  FDMemTable1.Filter := 'ID like ' + QuotedStr('A%');

  FDMemTable1.Filtered := True;

  _ValueSum := 0;

  FDMemTable1.FindFirst;

  for i := 0 to FDMemTable1.RecordCount - 1 do
  begin
    _ValueSum := _ValueSum + FDMemTable1.FieldValues['value'];

    FDMemTable1.FindNext;
  end;

  Button1.Caption := IntToStr(_ValueSum);
end;

我知道tDataSet.Locate不允许下一次搜索，所以我尝试了这样一种原始的方法。它工作得很好，但看起来有点愚蠢。

procedure TForm1.Button2Click(Sender: TObject);
var
  _ValueSum: Integer;
  i: Integer;
begin
  _ValueSum := 0;

  FDMemTable1.First;

  for i := 0 to FDMemTable1.RecordCount do
  begin
    if Copy(FDMemTable1.FieldValues['ID'], 1, 1) = 'A' then
    begin
      _ValueSum := _ValueSum + FDMemTable1.FieldValues['value'];
    end;

    FDMemTable1.FindNext;
  end;

  Button2.Caption := IntToStr(_ValueSum);
end;

当我断开tFDMemTable和tDBGrid的连接或在过滤前设置inactive以保存最后一个网格状态时，网格变为空白网格。最后的代码是最好的解决方案吗?或者有没有更好的方法在过滤工作时显示未过滤的结果？

Answer 1

FireDAC还有另一个有趣的选项--聚合：

procedure TForm1.Button1Click(Sender: TObject);
begin
  FDMemTable1.Aggregates.Clear;
  with FDMemTable1.Aggregates.Add do
  begin
    Name := 'SUM';
    Expression := 'sum(iif(ID like ''A%'', value, 0))';
    Active := True;
  end;
  FDMemTable1.AggregatesActive := True;
  FDMemTable1.Refresh;
  Button1.Caption := VarToStr(FDMemTable1.Aggregates[0].Value));
end;