HAVING 子句允许我们挑选出函数结果满足某些条件的特定行。
WHERE 子句在选定的列上放置条件，而 HAVING 子句在由 GROUP BY 子句创建的组上放置条件。
语法
以下是 SELECT 查询中 HAVING 子句的位置
SELECT FROM WHERE GROUP BY HAVING ORDER BY
HAVING 子句必须跟在查询中的 GROUP BY 子句之后，并且如果使用，还必须在 ORDER BY 子句之前。以下是 SELECT 语句的语法，包括 HAVING 子句
SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2
例子
考虑表pgccc_COMPANY 的记录如下
# select * from pgccc_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 的记录
LNXDB‐# SELECT NAME FROM pgccc_COMPANY GROUP BY name HAVING count(name) < 2;
这将产生以下结果
name ‐‐‐‐‐‐ Teddy Paul Mark David Allen Kim James (7 rows)
现在，让我们使用以下 INSERT 语句在 pgccc_COMPANY 表中再创建三个记录
INSERT INTO pgccc_COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00); INSERT INTO pgccc_COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00); INSERT INTO pgccc_COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
现在，我们的表有以下重复名称的记录
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 的记录
LNXDB=# SELECT NAME FROM pgccc_COMPANY GROUP BY name HAVING count(name) > 1;
这将产生以下结果
name ‐‐‐‐‐‐‐ Paul James (2 rows)