Oracle HAVING
在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函数。
aggregate_expression: 它指定聚合函数所基于的列或表达式。
table: 它指定要从中检索的表记录。
conditions: 它指定选择记录必须满足的条件。
conditions: 指定仅适用于汇总结果以限制返回的行的组的条件。
Oracle HAVING示例:(带有GROUP BY SUM函数)
取一个表"salesdepartment"
salesdepartment able:
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函数)
让我们看一个表"CUSTOMERS"
CUSTOMERS table:
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函数)
让我们以"EMPLOYEES"表为例
EMPLOYEES table:
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;
输出:
