SQL HAVING Clause

                 

SQL: HAVING Clause

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.

译： HAVING 子句是与 GROUP BY 子句联合使用。能够用于 SELECT 语句中用于筛选由 GROUP BY 返回的结果。

The syntax for the HAVING clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;

aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.

译： aggregate_function 可以是 SUM, COUNT, MIN, or MAX 函数。

Example using the SUM function

For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.

译：例如，你可以使用 SUN 函数返回以 department 为分组的总销售。 HAVING 子句就会选择销售大于 $1000 的结果返回。

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

 

Example using the COUNT function

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The HAVING clause will filter the results so that only departments with at least 25 employees will be returned.

译：例如，你可以使用 COUNT 函数返回以部门为单位的，工资超过 $25000/ 年的员工人数。 HAVING 子句将返回部门员工数满足该条件 25 人以上的结果集。

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;

 

Example using the MIN function

For example, you could also use the MIN function to return the name of each department and the minimum salary in the department. The HAVING clause will return only those departments where the starting salary is $35,000.

译：返回起始工资为 $35,000 的最低工资的结果集。

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;

 

Example using the MAX function

For example, you could also use the MAX function to return the name of each department and the maximum salary in the department. The HAVING clause will return only those departments whose maximum salary is less than $50,000.

译：返回最高工资为 50000 的最高工资的结果集。

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;

 

            

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！https://blog.csdn.net/jiangjunshow