HAVING
Clause allows us to filter the grouped data.
The following is Create the COMPANY table ( 下载 COMPANY SQL 文件 ), the data are as follows: The following example will find out the basis for The following results are obtained: We add a few pieces of data to the table: At this time The following example will find out the basis for The results are as follows:
WHERE
Clause sets the condition on the selected column, while the
HAVING
Clause sets the condition on the grouping created by the GROUP BY clause. 5.26.1. Grammar ¶
HAVING
Clause in
SELECT
Location in the query:SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
HAVING
The clause must be placed after the GROUP BY clause, before the ORDER BY clause, and here is
HAVING
The basic syntax of the clause in the SELECT statement:SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
5.26.2. Example ¶
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)
NAME
Field values are grouped, and name(名称) The count of fields is less than 2 data: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
The record of the table is as follows: 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
Field values are grouped, and the count of names is greater than 1 data:runoobdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;
name
-------
Paul
James
(2 rows)