#
第五十章 SQL命令 HAVING(一) 对一组数据值指定一个或多个限制性条件的`SELECT`子句。 # 大纲 ```sql SELECT field FROM table GROUP BY field HAVING condition-expression SELECT aggregatefunc(field %AFTERHAVING) FROM table [GROUP BY field] HAVING condition-expression ``` ## 参数 - `condition-expression` - 由一个或多个布尔谓词组成的表达式,用于控制要检索哪些数据值。 # 描述 可选的`HAVING`子句出现在`FROM`子句、可选的`WHERE`和`GROUP BY`子句之后,可选的`ORDER BY`子句之前。 `SELECT`语句的`HAVING`子句限定或取消查询选择中的特定行。符合条件的行是条件表达式为真的行。条件表达式是一系列逻辑测试(谓词),它们可以通过`AND`和`OR`逻辑运算符链接起来。 `HAVING`子句类似于`WHERE`子句,它可以在组上操作,而不是在整个数据集上操作。因此,在大多数情况下,`HAVING`子句要么与使用`%AFTERHAVING`关键字的聚合函数一起使用,要么与`GROUP BY`子句结合使用,或者两者兼而有之。 `HAVING`子句条件表达式还可以指定聚合函数。`WHERE`子句条件表达式不能指定聚合函数。下面的示例显示了这一点: ```sql SELECT Name,Age,AVG(Age) AS AvgAge FROM Sample.Person HAVING Age > AVG(Age) ORDER BY Age ``` ![image](3C145C3DA37F4B61815B17FB5096ED41) `HAVING`子句通常用于将子群体的聚合与整个群体的聚合进行比较。 ## 指定字段 `HAVING`子句条件表达式或`%AFTERHAVING`关键字表达式中指定的字段必须指定为字段名或聚合函数。不能按列号指定字段或聚合函数。不能按列别名指定字段或聚合函数;尝试这样做会生成`SQLCODE-29`错误。但是,可以使用子查询定义列别名,然后在`HAVING`子句中使用该别名。例如: ```sql SELECT Y AS TeenYear,AVG(Y %AFTERHAVING) AS AvgTeenAge FROM (SELECT Age AS Y FROM Sample.Person WHERE Age<20) HAVING Y > 12 ORDER BY Y ``` ![image](AD73AE178A0A4A9BA1C1BD2C5760D68B) ## 选择项列表中的聚合函数 `HAVING`子句选择要返回的行。默认情况下,此行选择不确定选择项列表中的聚合函数的值。这是因为`HAVING`子句在`SELECT-ITEM`列表中的聚合函数之后进行解析。 在下面的示例中,只返回`Age > 65`的行。但`AVG`(年龄)是基于所有行计算的,而不仅仅是`HAVING`子句选择的行: ```sql SELECT Name,Age,AVG(Age) AS AvgAge FROM Sample.Person HAVING Age > 65 ORDER BY Age ``` ![image](7647AC7A137944F98D41A7CCAF5D7DA0) 将它与`WHERE`子句进行比较,`WHERE`子句选择返回哪些行,以及在`select-item`列表的聚合函数中包含哪些行值: ```sql SELECT Name,Age,AVG(Age) AS AvgAge FROM Sample.Person WHERE Age > 65 ORDER BY Age ``` `HAVING`子句可以用于只返回聚合值的查询: - 聚合阈值`:HAVING`子句使用聚合阈值来确定是返回1行(包含查询聚合值)还是0行。 因此,可以使用`HAVING`子句只在达到聚合阈值时返回聚合计算。 下面的示例仅在表中至少有`100`行时返回表中所有行的`Age`值的平均值。 如果小于`100`行,所有行的`Age`值的平均值可能被认为没有意义,因此不应该返回: ```sql SELECT AVG(Age) FROM Sample.Person HAVING COUNT(*)>99 ``` ![image](81D4D97688FD4E6D99841B61276CE6FB) - 多行:带有聚合函数且没有`GROUP BY`子句的`HAVING`子句返回满足`HAVING`子句条件的行数。 聚合函数值是根据表中的所有行计算的: ```sql SELECT AVG(Age) FROM Sample.Person HAVING %ID<10 ``` ![image](2D7D2074BC774E5699A792CDDC4110B7) 这与带有聚合函数的`WHERE`子句相反,后者返回一行。 聚合函数值是根据满足`WHERE`子句条件的行计算的: ```sql SELECT AVG(Age) FROM Sample.Person HAVING %ID<10 ``` ![image](8B28FCFFE54347618D65E344EA10CCD6) ## %AFTERHAVING `%AFTERHAVING`关键字可以与选择项列表中的聚合函数一起使用,以指定在应用`HAVING`子句条件之后执行聚合操作。 ```sql SELECT Name,Age,AVG(Age) AS AvgAge, AVG(Age %AFTERHAVING) AS AvgMiddleAge FROM Sample.Person HAVING Age > 40 AND Age < 65 ORDER BY Age ``` ![image](31360BEC7DBF40669DD82D7F5FE6399E) 只有满足以下两个条件时,`%AFTERHAVING`关键字才会给出有意义的结果: - 选择项列表必须至少包含一个非聚合字段引用的项。 这个字段引用可以是`FROM`子句中指定的任何表中的任何字段、使用隐式连接(箭头语法)引用的字段、`%ID`别名或星号(`*`)。 - `HAVING`子句条件必须应用至少一个非聚合条件。 因此,有`HAVING Age>50`, `HAVING Age>AVG(Age)`, `or HAVING Age>50 AND MAX(Age)>75`是有效的条件,但有`HAVING Age>50 OR MAX(Age)>75`不是有效条件。 下面的示例使用带有`GROUP BY`子句的`HAVING`子句返回状态平均年龄,以及大于表中所有行平均年龄的人的状态平均年龄。 它还使用子查询返回表中所有行的平均年龄: ```sql SELECT Home_State,(SELECT AVG(Age) FROM Sample.Person) AS AvgAgeAllRecs, AVG(Age) AS AvgAgeByState,AVG(Age %AFTERHAVING) AS AvgOlderByState FROM Sample.Person GROUP BY Home_State HAVING Age > AVG(Age) ORDER BY Home_State ``` ![image](CD9AC46192374C93AAE566FE49AF65AB) # 逻辑谓词 SQL谓词可分为以下几类: - `Equality Comparison`谓词 - `BETWEEN`谓语 - `In`和`%INLIST`谓词 - `%STARTSWITH`谓词 - 包含运算符(`[`) - `FOR SOME`谓词 - `NULL` 谓词 - `EXISTS` 谓词 - `LIKE`, `%MATCHES`, `and %PATTERN` 谓词 - `%INSET and %FIND` 谓词 注意:不能在`HAVING`子句中使用`FOR SOME %ELEMENT`集合谓词。此谓词只能在`WHERE`子句中使用。 ## 谓词区分大小写 谓词使用为字段定义的排序规则类型。默认情况下,字符串数据类型字段使用`SQLUPPER`排序规则定义,该排序规则不区分大小写。 `%INLIST`、`CONTAINS`运算符(`[`)、`%Matches`和`%%PATTERN`谓词不使用字段的默认排序规则。它们总是使用精确排序,这是区分大小写的。 两个文字字符串的谓词比较始终区分大小写。 ## 谓词条件和%NOINDEX 可以使用`%NOINDEX`关键字作为谓词条件的前缀,以防止查询优化器在该条件上使用索引。 这在指定绝大多数行都满足的范围条件时非常有用。 例如,`HAVING %NOINDEX Age >= 1`。 ## 相等比较谓词 以下是可用的比较谓词: 谓词 |操作 ---|--- `=` |相等 `<>` |不相等 `!=` |不相等 `>` |大于 `<` |小于 `>=` |大于等于 `<=` |小雨等于 以下示例使用比较谓词。它为小于`21`岁的每个年龄返回一条记录: ```sql SELECT Name, Age FROM Sample.Person GROUP BY Age HAVING Age < 21 ORDER BY Age ``` ![image](BD2B174205A242B9A1A0EEB523D2ABC7) 请注意,SQL根据排序规则(值的排序顺序)定义比较操作。如果两个值以完全相同的方式排序,则它们相等。如果一个值在第二个值之后排序,则该值大于另一个值。字符串数据类型字段排序规则基于字段的默认排序规则。默认情况下,它不区分大小写。因此,两个字符串字段值的比较或字符串字段值与字符串文字的比较(默认情况下)不区分大小写。例如,如果`Home_State`字段值是由两个字母组成的大写字符串: Expression |Value ---|--- 'MA' = Home_State| TRUE for values MA. 'ma' = Home_State| TRUE for values MA. 'VA' < Home_State| TRUE for values VT, WA, WI, WV, WY. 'ar' >= Home_State| TRUE for values AK, AL, AR. 但是请注意,两个文字字符串的比较区分大小写:其中`'ma'='MA'`始终为`false`。 ## BETWEEN谓语 这等效于大于或等于且小于或等于的配对。下面的示例使用`BETWEEN`谓词。它为`18`到`35`岁(包括`18`到`35`岁)的每个年龄返回一条记录: ```sql SELECT Name, Age FROM Sample.Person GROUP BY Age HAVING Age BETWEEN 18 AND 35 ORDER BY Age ``` ![image](3F0D2A4A4D4140A6B6F283C1A5720740)