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 = 184end end

總結

以上是生活随笔為你收集整理的学习 SQL 语句 - Select(7): 分组统计之 Avg()、Sum()、Max()、Min()、Count()的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。