Postgresql 简明教程

PostgreSQL - HAVING Clause

HAVING 子句允许我们选择特定行，其中函数结果满足某个条件。

The HAVING clause allows us to pick out particular rows where the function’s result meets some condition.

WHERE 子句对所选列设置条件，而 HAVING 子句对 GROUP BY 子句创建的组设置条件。

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax

以下是在 SELECT 查询中 HAVING 子句的位置 −

The following is the position of the HAVING clause in a SELECT query −

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING 子句必须跟随查询中的 GROUP BY 子句，且如果使用，还必须位于 ORDER BY 子句之前。以下是 SELECT 语句的语法，其中包括 HAVING 子句 −

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause −

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Example

考虑 COMPANY 表具有以下记录：

Consider the table COMPANY having records as follows −

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下为一个示例，它将显示名称计数少于 2 的记录 −

The following is an example, which would display record for which the name count is less than 2 −

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

这将产生以下结果 -

This would produce the following result −

  name
 -------
  Teddy
  Paul
  Mark
  David
  Allen
  Kim
  James
(7 rows)

现在，让我们使用以下 INSERT 语句在 COMPANY 表中创建三条更多记录 −

Now, let us create three more records in COMPANY table using the following INSERT statements −

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

现在，我们的表有以下具有重复名称的记录 −

Now, our table has the following records with duplicate names −

  id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

以下为一个示例，它将显示名称计数大于 1 的记录 −

The following is the example, which would display record for which the name count is greater than 1 −

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;

这将产生以下结果 -

This would produce the following result −

 name
-------
 Paul
 James
(2 rows)