PostgreSQL HAVING 子句

HAVING 子句可以让我们筛选分组后的各组数据。

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

语法

下面是 HAVING 子句在 SELECT 查询中的位置：

1SELECT
2FROM
3WHERE
4GROUP BY
5HAVING
6ORDER BY

HAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，下面是 HAVING 子句在 SELECT 语句中基础语法：

1SELECT column1, column2
2FROM table1, table2
3WHERE [ conditions ]
4GROUP BY column1, column2
5HAVING [ conditions ]
6ORDER BY column1, column2

实例

创建 COMPANY 表（下载 COMPANY SQL 文件 ），数据内容如下：

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

下面实例将找出根据 NAME 字段值进行分组，并且 name(名称) 字段的计数少于 2 数据：

1SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

得到以下结果：

1name
2 -------
3  Teddy
4  Paul
5  Mark
6  David
7  Allen
8  Kim
9  James
10(7 rows)

我们往表里添加几条数据：

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

此时，COMPANY 表的记录如下：

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

下面实例将找出根据 name 字段值进行分组，并且名称的计数大于 1 数据：

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

得到结果如下：

1name
2-------
3 Paul
4 James
5(2 rows)