锐客网

  1. 首页 > 睿知 > it技术 > >

Oracle HAVING子句

OracleIT知识

本文概述

  • 参数
  • 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) ) /

Oracle HAVING子句

文章图片
执行此查询：
SELECT item, SUM(sale) AS "Total sales" FROM salesdepartment GROUP BY item HAVING SUM(sale) < 1000;

输出
Oracle HAVING子句

文章图片
Oracle HAVING示例：(具有GROUP BY COUNT函数) 让我们来一张桌子” 客户”
客户表：
CREATE TABLE"CUSTOMERS" ( "NAME" VARCHAR2(4000), "AGE" NUMBER, "SALARY" NUMBER, "STATE" VARCHAR2(4000) ) /

Oracle HAVING子句

文章图片
执行此查询：
SELECT state, COUNT(*) AS "Number of customers" FROM customers WHERE salary > 10000 GROUP BY state HAVING COUNT(*) > = 2;

输出
Oracle HAVING子句

文章图片
Oracle HAVING示例：(具有GROUP BY MIN函数) 让我们来一张桌子” 员工”
员工表：
CREATE TABLE"EMPLOYEES" ( "EMP_ID" NUMBER, "NAME" VARCHAR2(4000), "AGE" NUMBER, "DEPARTMENT" VARCHAR2(4000), "SALARY" NUMBER ) /

Oracle HAVING子句

文章图片
执行此查询：
SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department HAVING MIN(salary) < 15000;

输出
Oracle HAVING子句

文章图片
Oracle HAVING示例：(具有GROUP BY MAX函数) 执行此查询：
SELECT department, MAX(salary) AS "Highest salary" FROM employees GROUP BY department HAVING MAX(salary) > 30000;

【Oracle HAVING子句】输出
Oracle HAVING子句

文章图片

    推荐阅读


    上一篇：Oracle插入语句使用实例

    下一篇：Oracle INSERT ALL语句