HAVING子句允许我们选择函数结果满足某些条件的特定行.
WHERE子句在所选列上放置条件,而HAVING子句在条件上放置条件由GROUP BY子句创建.
以下是SELECT查询中HAVING子句的位置 :
SELECT FROM WHERE GROUP BY HAVING ORDER BY
HAVING子句必须遵循查询中的GROUP BY子句,并且必须在ORDER BY子句之前使用(如果使用).以下是SELECT语句的语法,包括HAVING子句 :
SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2
考虑表公司记录如下 :
# 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)
以下是一个示例,它将显示名称计数小于2的记录;
testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;
这将产生以下结果 :
name ------- Teddy Paul Mark David Allen Kim James (7 rows)
现在,让我们在COMPANY表中使用以下INSERT语句创建另外三条记录 :
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);
现在,我们的表格中包含以下重复名称的记录;
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)
以下是示例,显示名称计数大于1的记录;
testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;
这将产生以下结果 :
name ------- Paul James (2 rows)