GROUP BY 子句 (Microsoft Access SQL)GROUP BY clause (Microsoft Access SQL)
06/08/2017
本文内容
适用于： Access 2013 | Access 2016Applies to: Access 2013 | Access 2016
将指定字段列表中具有相同值的记录组合成一个记录。Combines records with identical values in the specified field list into a single record. 如果在 SELECT 语句中包含 SQL 聚合函数(如 Sum 或 Count)，则为每条记录创建一个汇总值。A summary value is created for each record if you include an SQL aggregate function, such as Sum or Count, in the SELECT statement.
语法Syntax
从_表_中选择 " fieldlist "。条件[GROUP BY groupfieldlist ]SELECT fieldlist FROM table WHERE criteria [GROUP BY groupfieldlist ]
包含了 GROUP BY 子句的 SELECT 语句具有以下部分：A SELECT statement containing a GROUP BY clause has these parts:
PartPart
说明Description
fieldlistfieldlist
要检索的字段的名称，以及任何字段名别名、SQL 聚合函数、选择谓词(ALL、DISTINCT、DISTINCTROW 或 TOP)或其他 SELECT 语句选项。The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
tabletable
要从中检索记录的表的名称。The name of the table from which records are retrieved. 有关详细信息，请参阅 FROM 子句。For more information, see the FROM clause.
criteriacriteria
选择条件。Selection criteria. 如果语句包含 WHERE 子句，那么 Microsoft Access 数据库引擎将在向记录应用 WHERE 条件后对值进行分组。If the statement includes a WHERE clause, the Microsoft Access database engine groups values after applying the WHERE conditions to the records.
groupfieldlistgroupfieldlist
最多 10 个用于分组记录的字段的名称。The names of up to 10 fields used to group records. _Groupfieldlist_中字段名称的顺序决定了分组级别从最高到最低级别的分组级别。The order of the field names in groupfieldlist determines the grouping levels from the highest to the lowest level of grouping.
备注Remarks
GROUP BY 是可选的。GROUP BY is optional.
如果在 SELECT 语句中没有 SQL 聚合函数，将省略汇总值。Summary values are omitted if there is no SQL aggregate function in the SELECT statement.
在 GROUP BY 字段中的 Null 值会被分组，不会被忽略。Null values in GROUP BY fields are grouped and are not omitted. 但是，任何 SQL 聚合函数都不会计算 Null。However, Null values are not evaluated in any SQL aggregate function.
通过 WHERE 子句可以排除不需要进行分组的行，通过 HAVING 子句可以筛选已经分组的记录。Use the WHERE clause to exclude rows you do not want grouped, and use the HAVING clause to filter records after they have been grouped.
假定 SELECT 语句至少包含一个 SQL 聚合函数，那么，除非 GROUP BY 字段列表中的某个字段包含 Memo 或 OLE 对象数据，否则该字段可以引用 FROM 子句列出的任何表中的任何字段，即使该字段并不包含在 SELECT 语句中。Unless it contains Memo or OLE Object data, a field in the GROUP BY field list can refer to any field in any table listed in the FROM clause, even if the field is not included in the SELECT statement, provided the SELECT statement includes at least one SQL aggregate function. Microsoft® Jet 数据库引擎不能对 Memo 或 OLE 对象字段进行分组。The Microsoft® Jet database engine cannot group on Memo or OLE Object fields.
在 SELECT 字段列表中的所有字段必须包含于 GROUP BY 子句中，或作为参数包含于 SQL 聚合函数中。All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.
示例Example
以下示例创建一个列表，列出各项唯一的职务以及担任每项职务的雇员数。This example creates a list of unique job titles and the number of employees with each title. 它调用 EnumFields 过程，你可以在 SELECT 语句示例中找到该过程。It calls the EnumFields procedure, which you can find in the SELECT statement example.
Sub GroupByX1()
Dim dbs As Database, rst As Recordset
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' For each title, count the number of employees
' with that title.
Set rst = dbs.OpenRecordset("SELECT Title, " _
& "Count([Title]) AS Tally " _
& "FROM Employees GROUP BY Title;")
' Populate the Recordset.
rst.MoveLast
' Call EnumFields to print the contents of the
' Recordset. Pass the Recordset object and desired
' field width.
EnumFields rst, 25
dbs.Close
End Sub对于每项唯一职务，以下示例计算 Washington 地区担任该项职务的雇员数。For each unique job title, this example calculates the number of employees in Washington who have that title.
Sub GroupByX2()
Dim dbs As Database, rst As Recordset
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' For each title, count the number of employees
' with that title. Only include employees in the
' Washington region.
Set rst = dbs.OpenRecordset("SELECT Title, " _
& "Count(Title) AS Tally " _
& "FROM Employees WHERE Region = 'WA' " _
& "GROUP BY Title;")
' Populate the Recordset.
rst.MoveLast
' Call EnumFields to print the contents of the
' Recordset. Pass the Recordset object and desired
' field width.
EnumFields rst, 25
dbs.Close
End Sub另请参阅See also
支持和反馈Support and feedback
有关于 Office VBA 或本文档的疑问或反馈？Have questions or feedback about Office VBA or this documentation? 请参阅 Office VBA 支持和反馈，获取有关如何接收支持和提供反馈的指南。Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
