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 檔 ），數據內容如下：

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

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

得到結果如下：

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