Oracle HAVING子句

本文概述

• 参数
• Oracle HAVING示例：(具有GROUP BY SUM函数)
• Oracle HAVING示例：(具有GROUP BY COUNT函数)
• Oracle HAVING示例：(具有GROUP BY MIN函数)
• Oracle HAVING示例：(具有GROUP BY MAX函数)

在Oracle中, HAVING子句与GROUP BY子句一起使用以限制条件为TRUE的返回行的组。

句法：

SELECT expression1, expression2, ... expression_n, aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING having_condition;

参数

expression1, expression2, … expression_n：它指定未封装在聚合函数中的表达式。这些表达式必须包含在GROUP BY子句中。

Aggregate_function：指定聚合函数, 即SUM, COUNT, MIN, MAX或AVG函数。

gregation_expression：指定聚合函数所基于的列或表达式。

表：它指定要从中检索记录的表。

条件：它指定要选择的记录必须满足的条件。

have_conditions：指定仅适用于汇总结果的条件, 以限制返回的行的组。

Oracle HAVING示例：(具有GROUP BY SUM函数)

让我们来一张桌子” salesdepartment”

销售部门表：

CREATE TABLE  "SALESDEPARTMENT" 
   (	"ITEM" VARCHAR2(4000), "SALE" NUMBER, "BILLING_ADDRESS" VARCHAR2(4000)
   )
/

执行此查询：

SELECT item, SUM(sale) AS "Total sales"
FROM salesdepartment
GROUP BY item
HAVING SUM(sale) < 1000;

输出

Oracle HAVING示例：(具有GROUP BY COUNT函数)

让我们来一张桌子”客户”

客户表：

CREATE TABLE  "CUSTOMERS" 
   (	"NAME" VARCHAR2(4000), "AGE" NUMBER, "SALARY" NUMBER, "STATE" VARCHAR2(4000)
   )
/

执行此查询：

SELECT state, COUNT(*) AS "Number of customers"
FROM customers
WHERE salary > 10000
GROUP BY state
HAVING COUNT(*) >= 2;

输出

Oracle HAVING示例：(具有GROUP BY MIN函数)

让我们来一张桌子”员工”

员工表：

CREATE TABLE  "EMPLOYEES" 
   (	"EMP_ID" NUMBER, "NAME" VARCHAR2(4000), "AGE" NUMBER, "DEPARTMENT" VARCHAR2(4000), "SALARY" NUMBER
   )
/

执行此查询：

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) < 15000;

输出

Oracle HAVING示例：(具有GROUP BY MAX函数)

执行此查询：

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) > 30000;

输出