全部产品

HAVING

更新时间:2018-12-02 19:17:22

HAVING子句与聚合函数以及GROUP BY子句共同使用,用来去掉不满足条件的分组。在分组和聚合计算完成后,HAVING对分组进行过滤。

  1. [ HAVING condition ]

以下示例查询CUSTOMER表,并进行分组,查出账户余额大于指定值的记录:

  1. SELECT count(*), mktsegment, nationkey,
  2. CAST(sum(acctbal) AS bigint) AS totalbal
  3. FROM customer
  4. GROUP BY mktsegment, nationkey
  5. HAVING sum(acctbal) > 5700000
  6. ORDER BY totalbal DESC;
  7. _col0 | mktsegment | nationkey | totalbal
  8. -------+------------+-----------+----------
  9. 1272 | AUTOMOBILE | 19 | 5856939
  10. 1253 | FURNITURE | 14 | 5794887
  11. 1248 | FURNITURE | 9 | 5784628
  12. 1243 | FURNITURE | 12 | 5757371
  13. 1231 | HOUSEHOLD | 3 | 5753216
  14. 1251 | MACHINERY | 2 | 5719140
  15. 1247 | FURNITURE | 8 | 5701952
  16. (7 rows)

注意事项

  • HAVING子句条件中引用的列必须为分组列或引用了聚合函数结果的列。

  • HAVING子句必须与聚合函数以及GROUP BY子句一起使用,用来对GROUP BY的分组进行过滤,去掉不满足条件的分组。