PostgreSQL HAVING 子句

HAVING 子句可以讓我們篩選分組后的各組數據。

WHERE 子句在所選列上設置條件，而 HAVING 子句則在由 GROUP BY 子句創(chuàng)建的分組上設置條件。

語法

下面是 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

實例

創(chuàng)建 COMPANY 表 ，數據內容如下：

w3cschooldb# 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 數據：

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

得到結果如下：

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