Group By
本来 Group By 也可以根据多个字段分组("," 隔开), 但这个例子只有 Continent 字段适合分组,
也只有 Area、Population 字段适合统计.
本例效果图:
代码文件:
unit Unit1;interfaceusesWindows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,Dialogs, StdCtrls, ExtCtrls, Grids, DBGrids, DB, ADODB;typeTForm1 = class(TForm)DBGrid1: TDBGrid;DataSource1: TDataSource;ADODataSet1: TADODataSet;Panel1: TPanel;Button1: TButton;Button2: TButton;Button3: TButton;Button4: TButton;Button5: TButton;procedure FormCreate(Sender: TObject);procedure Button1Click(Sender: TObject);procedure Button2Click(Sender: TObject);procedure Button3Click(Sender: TObject);procedure Button4Click(Sender: TObject);procedure Button5Click(Sender: TObject);end;varForm1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);beginwith ADODataSet1 do beginClose;CommandText := 'SELECT Continent, ' +'AVG(Area) AS 平均面积, AVG(Population) AS 平均人口 ' +'FROM country GROUP BY Continent';Open;end;end;procedure TForm1.Button2Click(Sender: TObject);beginwith ADODataSet1 do beginClose;CommandText := 'SELECT Continent, ' +'SUM(Area) AS 面积总数, SUM(Population) AS 人口总数 ' +'FROM country GROUP BY Continent';Open;end;end;procedure TForm1.Button3Click(Sender: TObject);beginwith ADODataSet1 do beginClose;CommandText := 'SELECT Continent, ' +'MAX(Area) AS 面积最大, MIN(Population) AS 人口最少 ' +'FROM country GROUP BY Continent';Open;end;end;procedure TForm1.Button4Click(Sender: TObject);beginwith ADODataSet1 do beginClose;CommandText := 'SELECT Continent, ' +'MAX(Area + Population) AS 面积与人口总和最多, ' +'MIN(Area + Population) AS 面积与人口总和最少 ' +'FROM country GROUP BY Continent';Open;end;end;procedure TForm1.Button5Click(Sender: TObject);beginwith ADODataSet1 do beginClose;CommandText := 'SELECT COUNT(*) AS 记录总数 FROM country';Open;end;end;procedure TForm1.FormCreate(Sender: TObject);varmdbFile: string;beginmdbFile := GetEnvironmentVariable('COMMONPROGRAMFILES');mdbFile := mdbFile + '\CodeGear Shared\Data\dbdemos.mdb';ADODataSet1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' +mdbFile + ';Persist Security Info=False';DBGrid1.DataSource := DataSource1;DataSource1.DataSet := ADODataSet1;end;end.
窗体文件:
object Form1: TForm1Left = 0Top = 0Caption = 'Form1'ClientHeight = 407ClientWidth = 626Color = clBtnFaceFont.Charset = DEFAULT_CHARSETFont.Color = clWindowTextFont.Height = -11Font.Name = 'Tahoma'Font.Style = []OldCreateOrder = FalseOnCreate = FormCreatePixelsPerInch = 96TextHeight = 13object DBGrid1: TDBGridLeft = 0Top = 33Width = 626Height = 374Align = alClientDataSource = DataSource1TabOrder = 0TitleFont.Charset = DEFAULT_CHARSETTitleFont.Color = clWindowTextTitleFont.Height = -11TitleFont.Name = 'Tahoma'TitleFont.Style = []endobject Panel1: TPanelLeft = 0Top = 0Width = 626Height = 33Align = alTopCaption = 'Panel1'TabOrder = 1object Button1: TButtonLeft = 6Top = 5Width = 75Height = 25Caption = 'Button1'TabOrder = 0OnClick = Button1Clickendobject Button2: TButtonLeft = 87Top = 5Width = 75Height = 25Caption = 'Button2'TabOrder = 1OnClick = Button2Clickendobject Button3: TButtonLeft = 168Top = 5Width = 75Height = 25Caption = 'Button3'TabOrder = 2OnClick = Button3Clickendobject Button4: TButtonLeft = 249Top = 5Width = 75Height = 25Caption = 'Button4'TabOrder = 3OnClick = Button4Clickendobject Button5: TButtonLeft = 330Top = 5Width = 75Height = 25Caption = 'Button5'TabOrder = 4OnClick = Button5Clickendendobject DataSource1: TDataSourceDataSet = ADODataSet1Left = 184Top = 112endobject ADODataSet1: TADODataSetCursorType = ctStaticParameters = <>Left = 232Top = 184endend
