PostgreSQL HAVING 子句

HAVING 子句可以讓我們篩選分組后的各組數(shù)據(jù)。

創(chuàng)新互聯(lián)公司是一家專注于網(wǎng)站設(shè)計、網(wǎng)站建設(shè)與策劃設(shè)計,吳江網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)10年,網(wǎng)設(shè)計領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:吳江等地區(qū)。吳江做網(wǎng)站價格咨詢:18980820575

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

語法

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

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING 子句必須放置于 GROUP BY 子句后面，ORDER BY 子句前面，下面是 HAVING 子句在 SELECT 語句中基礎(chǔ)語法：

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

實(shí)例

創(chuàng)建 COMPANY 表（下載 COMPANY SQL 文件 ），數(shù)據(jù)內(nèi)容如下：

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

下面實(shí)例將找出根據(jù) NAME 字段值進(jìn)行分組，并且 name(名稱) 字段的計數(shù)少于 2 數(shù)據(jù)：

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

得到以下結(jié)果：

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

我們往表里添加幾條數(shù)據(jù)：

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)

下面實(shí)例將找出根據(jù) name 字段值進(jìn)行分組，并且名稱的計數(shù)大于 1 數(shù)據(jù)：

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

得到結(jié)果如下：

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

分享題目：創(chuàng)新互聯(lián)PostgreSQL教程PostgreSQLHAVING子句
標(biāo)題網(wǎng)址：http://m.5511xx.com/article/dppdgcj.html