sqlite 本身没有这个功能,FireDAC 通过 TFDsqliteFunction 增加了该功能; 尽管通过某些 sql 语句或通过视图也可以达到类似效果,但函数会更灵活些.

本例先建了一个成绩表,然后通过两个 TFDsqliteFunction 实现了 "总分" 与 "平均分" 的计算.

---

你可以复制下面文本框中的内容,然后直接往窗体上贴,以快速完成窗体设计:
object DBGrid1: TDBGrid
Left = 8
Top = 88
Width = 321
Height = 89
DataSource = DataSource1
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
end
object Button1: TButton
Left = 382
Top = 88
Width = 75
Height = 25
Caption = 'Button1'
TabOrder = 1
OnClick = Button1Click
end
object Button2: TButton
Left = 382
Top = 129
Width = 75
Height = 25
Caption = 'Button2'
TabOrder = 2
OnClick = Button2Click
end
object FDConnection1: TFDConnection
Left = 34
Top = 24
end
object FDPhyssqliteDriverLink1: TFDPhyssqliteDriverLink
Left = 143
Top = 24
end
object FDGUIxWaitCursor1: TFDGUIxWaitCursor
Provider = 'Forms'
Left = 260
Top = 24
end
object FDQuery1: TFDQuery
Connection = FDConnection1
Left = 344
Top = 24
end
object DataSource1: TDataSource
DataSet = FDQuery1
Left = 420
Top = 24
end
object FDsqliteFunction1: TFDsqliteFunction
DriverLink = FDPhyssqliteDriverLink1
Active = True
FunctionName = 'MyFun1'
ArgumentsCount = 3
OnCalculate = FDsqliteFunction1Calculate
Left = 48
Top = 200
end
object FDsqliteFunction2: TFDsqliteFunction
DriverLink = FDPhyssqliteDriverLink1
Active = True
FunctionName = 'MyFun2'
ArgumentsCount = 3
OnCalculate = FDsqliteFunction2Calculate
Left = 152
Top = 200
end

---

代码:

---

unit Unit1;

interface

uses
 Winapi.Windows,Winapi.Messages,System.SysUtils,System.Variants,System.Classes,Vcl.Graphics,Vcl.Controls,Vcl.Forms,Vcl.Dialogs,FireDAC.Stan.Intf,FireDAC.Stan.Option,FireDAC.Stan.Error,FireDAC.UI.Intf,FireDAC.Phys.Intf,FireDAC.Stan.Def,FireDAC.Stan.Pool,FireDAC.Stan.Async,FireDAC.Phys,FireDAC.Stan.ExprFuncs,FireDAC.VCLUI.Wait,FireDAC.Stan.Param,FireDAC.DatS,FireDAC.DApt.Intf,FireDAC.DApt,Vcl.Grids,Vcl.DBGrids,Data.DB,FireDAC.Comp.DataSet,FireDAC.Comp.Client,FireDAC.Comp.UI,FireDAC.Phys.sqlite,Vcl.StdCtrls,FireDAC.Phys.sqliteWrapper;

type
 TForm1 = class(TForm)
  FDConnection1: TFDConnection;
  FDPhyssqliteDriverLink1: TFDPhyssqliteDriverLink;
  FDGUIxWaitCursor1: TFDGUIxWaitCursor;
  FDQuery1: TFDQuery;
  DataSource1: TDataSource;
  DBGrid1: TDBGrid;
  Button1: TButton;
  Button2: TButton;
  FDsqliteFunction1: TFDsqliteFunction;
  FDsqliteFunction2: TFDsqliteFunction;
  procedure FormCreate(Sender: TObject);
  procedure Button1Click(Sender: TObject);
  procedure Button2Click(Sender: TObject);
  procedure FDsqliteFunction1Calculate(AFunc: TsqliteFunctionInstance; AInputs: TsqliteInputs; AOutput: TsqliteOutput; var AUserData: TObject);
  procedure FDsqliteFunction2Calculate(AFunc: TsqliteFunctionInstance; AInputs: TsqliteInputs; AOutput: TsqliteOutput; var AUserData: TObject);
 private
  { Private declarations }
 public
  { Public declarations }
 end;

var
 Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
const
 strTable = 'CREATE TABLE MyTable(姓名 string(10),语文 Integer,数学 Integer,英语 Integer)'; // 建一个学生成绩表
begin
 { 建立一个成绩表,并插入测试数据 }
 FDConnection1.Params.Add('DriverID=sqlite');
 FDConnection1.Execsql(strTable);
 FDQuery1.Execsql('INSERT INTO MyTable(姓名,语文,数学,英语) VALUES(:1,:2,:3,:4)',['张三',66,77,88]);
 FDQuery1.Execsql('INSERT INTO MyTable(姓名,['李四',88,99]);
 FDQuery1.Open('SELECT * FROM MyTable');

 { 分别给两个 TFDsqliteFunction 设定参数 }
 FDsqliteFunction1.DriverLink := FDPhyssqliteDriverLink1;
 FDsqliteFunction1.FunctionName := 'MyFun1'; // 函数名
 FDsqliteFunction1.ArgumentsCount := 3; // 函数的参数个数
 // FDsqliteFunction1.OnCalculate := FDsqliteFunction1Calculate; //在设计时建立 OnCalculate 事件更方便
 FDsqliteFunction1.Active := True;

 FDsqliteFunction2.DriverLink := FDPhyssqliteDriverLink1;
 FDsqliteFunction2.FunctionName := 'MyFun2';
 FDsqliteFunction2.ArgumentsCount := 3;
 // FDsqliteFunction2.OnCalculate := FDsqliteFunction2Calculate; //在设计时建立 OnCalculate 事件更方便
 FDsqliteFunction2.Active := True;
end;

{ 调用 MyFun1 }
procedure TForm1.Button1Click(Sender: TObject);
begin
 FDQuery1.Open('SELECT 姓名,MyFun1(语文,英语) AS 总分 FROM MyTable');
end;

{ 调用 MyFun2 }
procedure TForm1.Button2Click(Sender: TObject);
begin
 FDQuery1.Open('SELECT 姓名,MyFun2(语文,英语) AS 平均分 FROM MyTable');
end;

{ 函数 MyFun1 的定义: 算总分 }
procedure TForm1.FDsqliteFunction1Calculate(AFunc: TsqliteFunctionInstance; AInputs: TsqliteInputs; AOutput: TsqliteOutput; var AUserData: TObject);
begin
 AOutput.AsInteger := AInputs[0].AsInteger + AInputs[1].AsInteger + AInputs[2].AsInteger;
end;

{ 函数 MyFun2 的定义: 算平均分 }
procedure TForm1.FDsqliteFunction2Calculate(AFunc: TsqliteFunctionInstance; AInputs: TsqliteInputs; AOutput: TsqliteOutput; var AUserData: TObject);
begin
 AOutput.AsFloat := (AInputs[0].AsInteger + AInputs[1].AsInteger + AInputs[2].AsInteger) / 3;
end;

end.

---

效果图:
原文链接：https://www.f2er.com/sqlite/199552.html