PostgreSQL中,HAVING子句与GROUP BY子句组合使用,用于选择函数结果满足某些条件的特定行。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
语法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
示例1:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
我们来看一下表“EMPLOYEES
”,具有以下数据。
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
在这个例子中,它将显示名称(name
)数量小于2
的记录。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
执行以下查询:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
SELECT NAME
FROM EMPLOYEES
GROUP BY NAME HAVING COUNT (NAME) < 2;
得到结果如下 -文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
示例2:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
我们在“EMPLOYEES
”表中插入一些重复的记录,首先添加以下数据:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
INSERT INTO EMPLOYEES VALUES (7, 'Minsu', 24, 'Delhi', 135000);
INSERT INTO EMPLOYEES VALUES (8, 'Manisha', 19, 'Noida', 125000);
现在完整的数据如下所示 -文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
执行以下查询表“EMPLOYEES
”中name
字段值计数大于1
的名称。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
SELECT NAME,COUNT (NAME)
FROM EMPLOYEES
GROUP BY NAME HAVING COUNT (NAME) > 1;
得到结果如下 -文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html
这是因为名字为 Minsu
和 Manisha
有两条记录。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4863.html