PostgreSQL HAVING 子句

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

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

语法

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

									SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
								

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

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

实例

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

									yssmxdb# 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)
								

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

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

得到以下结果：

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

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

									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);
								

此时，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
   8_Paul _ 24_Houston     _ 20000
   9_James_ 44_Norway      _  5000
  10_James_ 45_Texas       _  5000
(10 rows)
								

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

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

得到结果如下：

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