Mysql 简明教程

MySQL - Having Clause

MySQL Having Clause

MySQL HAVING Clause 用于根据条件过滤表中分组的行。

The MySQL HAVING Clause is used to filter grouped rows in a table based on conditions.

这个子句与 GROUP BY 子句一起使用，根据一个或多个列对行进行分组，然后根据 HAVING 子句中指定的条件对它们进行过滤。因此，HAVING 子句必须始终跟随 GROUP BY 子句。

This clause is used with the GROUP BY clause to group the rows based on one or more columns and then filter them based on the conditions specified in the HAVING clause. So, the HAVING clause must always be followed by the GROUP BY clause.

添加 HAVING 子句到 MySQL 中，是因为 WHERE 关键字不能与 COUNT()、SUM()、AVG() 等聚合函数一起使用。

The HAVING clause was added to MySQL because the WHERE keyword cannot be used with aggregate functions such as COUNT(), SUM(), AVG(), etc.

此子句类似于 MySQL WHERE 子句。它们之间的区别在于 WHERE 子句过滤表中的单个行，而 HAVING 子句根据条件过滤分组行。

This clause is similar to the MySQL WHERE clause. The difference between both of them is that the WHERE clause filters individual rows in a table, whereas the HAVING clause filters grouped rows based on conditions.

Syntax

下面是 MySQL 中 HAVING 子句的基本语法 −

Following is the basic syntax of the HAVING clause in MySQL −

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1, column2, ...;

Example

让我们从使用下面的查询创建名为 CUSTOMERS 的表开始 −

Let us begin with creating a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

下面的 INSERT 语句将 7 条记录插入到上面创建的表中 −

The following INSERT statement inserts 7 records into the above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

使用以下查询，我们可以验证 CUSTOMERS 表是否创建 −

Using the following query, we can verify whether the CUSTOMERS table is created or not −

SELECT * FROM CUSTOMERS;

以下为 CUSTOMERS 表 −

Following is the CUSTOMERS table −

HAVING clause with ORDER BY clause

在 MySQL 中，HAVING 子句过滤组，而 ORDER BY 子句对结果进行排序。当我们一起使用它们时，HAVING 先执行，然后结果集根据 ORDER BY 标准进行排序。

In MySQL, the HAVING clause filters the groups, and the ORDER BY clause sorts the results. When we used both of them together, HAVING is executed first, then the result set is sorted according to the ORDER BY criteria.

Example

在下面的查询中，我们检索 CUSTOMERS 表中的所有记录，其中 SALARY 总和小于 4540，按名称升序排列 −

In the following query, we are retrieving all the records from the CUSTOMERS table where the sum of their SALARY is less than 4540, ordered by their name in ascending order −

SELECT NAME, SUM(SALARY) as total_salary
FROM CUSTOMERS
GROUP BY NAME
HAVING SUM(SALARY) < 4540
ORDER BY NAME;

Output

以上查询的输出如下所示：

The output for the query above is produced as given below −

HAVING clause with COUNT() function

我们可以将 MySQL HAVING 子句与 COUNT() 函数结合使用，根据组中包含的行数过滤组。

We can use the MySQL HAVING clause in conjunction with the COUNT() function to filter the groups based on the number of rows they contain.

Example

在这个查询中，我们获取一个记录，年龄相似的数量大于或等于 2。

In this query, we are fetching a record where the count of similar age is greater than or equal to 2.

SELECT AGE
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;

Output

CUSTOMERS 表中有两条记录年龄为 25，因此输出为 25 −

There are two records in CUSTOMERS table with age 25, thus the output is 25 −

HAVING clause with AVG() function

MySQL HAVING 子句也可以与 AVG() 函数一起使用，以根据指定列的平均值过滤组。

The MySQL HAVING clause can also be used with the AVG() function to filter groups based on the average value of a specified column.

Example

在以下查询中，我们尝试返回工资高于 3000 的客户姓名 −

In the following query, we are trying to return the names of the customers whose salary is greater than 3000 −

SELECT NAME, AVG(salary) as avg_salary
FROM customers
GROUP BY NAME
HAVING AVG(salary) > 3000;

Output

以上查询的输出如下所示：

The output for the query above is produced as given below −

HAVING clause with MAX() function

在 MySQL 中，我们也可以使用 HAVING 子句与 MAX() 函数一起，根据指定列的最大值过滤组。

In MySQL, we can also use the HAVING clause with MAX() function to filter groups based on the maximum value of a specified column.

Example

在这个查询中，我们检索最大 SALARY 小于 4000 的客户姓名 −

In this query, we are retrieving the customer names whose maximum SALARY is less than 4000 −

SELECT NAME, MAX(salary) as max_salary
FROM customers
GROUP BY NAME
HAVING MAX(salary) < 4000;

Output

执行给定的查询后，输出如下：

On executing the given query, the output is displayed as follows −

Having Clause Using a Client Program

除了使用 MySQL HAVING 子句基于条件过滤表中的分组行，我们还可以使用 Node.js、PHP、Java 和 Python 等客户端程序实现相同的结果。

Besides using MySQL HAVING clause to filter grouped rows in a table based on conditions, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

以下是此操作在各种编程语言中的语法 −

Following are the syntaxes of this operation in various programming languages −

Example

以下是这些程序 −

Following are the programs −