Table of contents
TOC
折叠目录
展开目录

HAVING 子句 (Microsoft Access SQL)HAVING Clause (Microsoft Access SQL)

office 365 dev account|上次更新日期: 2018/4/3
|
2 参与人员

适用于: Access 2013 | Access 2016Applies to: Access 2013 | Access 2016

本文在语法说明示例In this articleSyntaxRemarks Example

指定在使用 GROUP BY 子句的 SELECT 语句中显示哪些分组记录。在GROUP BY 组合记录后,HAVING 显示由 GROUP BY 子句分组的记录中满足 HAVING 子句条件的任何记录。Specifies which grouped records are displayed in a SELECT statement with a GROUP BY clause. AfterGROUP BY combines records, HAVING displays any records grouped by the GROUP BY clause that satisfy the conditions of the HAVING clause.

语法Syntax

选择_字段列表_从_表_ selectcriteriagroupfieldlist [HAVING groupcriteria ] 按组的位置SELECT fieldlist FROM table WHERE selectcriteria GROUP BY groupfieldlist [HAVING groupcriteria ]

包含 HAVING 子句的 SELECT 语句具有以下部分:A SELECT statement containing a HAVING clause has these parts:

部分Part说明Description
字段列表fieldlist任何字段名称的别名, SQL 聚合函数,以及要检索的字段的名称选择谓词 (所有,互异,顶部或 DISTINCTROW,),或 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.
selectcriteriaselectcriteria选择条件。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.
groupcriteriagroupcriteria一个表达式,确定要显示哪些分组记录。An expression that determines which grouped records to display.

说明Remarks

HAVING 是可选的。HAVING is optional.

HAVING 与 WHERE 相似,WHERE 确定哪些记录会被选中。通过 GROUP BY 对记录分组后,HAVING 确定将显示哪些记录:HAVING is similar to WHERE, which determines which records are selected. After records are grouped with GROUP BY, HAVING determines which records are displayed:

SELECT CategoryID, 
Sum(UnitsInStock) 
FROM Products 
GROUP BY CategoryID 
HAVING Sum(UnitsInStock) > 100 And Like "BOS*";

HAVING 子句可以包含达 40 个由逻辑运算符,例如AndOr链接的表达式。A HAVING clause can contain up to 40 expressions linked by logical operators, such as And and Or.

示例Example

以下示例选择由 Washington 地区的多个雇员担任的职务。This example selects the job titles assigned to more than one employee in the Washington region.

以下示例调用 EnumFields 过程,您可以在 SELECT 语句示例中找到该过程。This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub HavingX() 

    Dim dbs As Database, rst As Recordset 

    ' Modify this line to include the path to Northwind 
    ' on your computer. 
    Set dbs = OpenDatabase("Northwind.mdb") 

    ' Select the job titles assigned to more than one  
    ' employee in the Washington region.  
    Set rst = dbs.OpenRecordset("SELECT Title, " _ 
        &; "Count(Title) as Total FROM Employees " _ 
        &; "WHERE Region = 'WA' " _ 
        &; "GROUP BY Title HAVING Count(Title) > 1;") 

    ' Populate the Recordset. 
    rst.MoveLast 

    ' Call EnumFields to print recordset contents. 
    EnumFields rst, 25 

    dbs.Close 

End Sub 

访问支持资源ACCESS SUPPORT RESOURCES
访问 MSDN 上的开发人员论坛Access for developers forum on MSDN
访问 support.office.com 上的帮助Access help on support.office.com
访问 answers.microsoft.com 上的帮助Access help on answers.microsoft.com
在必应上搜索特定的访问错误代码Search for specific Access error codes on Bing
访问 UtterAccess 上的论坛Access forums on UtterAccess
访问 UtterAcess 上的 wikiAccess wiki on UtterAcess
访问开发人员和 VBA 编程帮助中心 (FMS)Access developer and VBA programming help center (FMS)
访问 StackOverflow 上的帖子Access posts on StackOverflow

© 2018 Microsoft