A2-02-19.DML-MySQL HAVING
转载自：http://www.mysqltutorial.org/mysql-having.aspx
MySQL HAVING
Summary: in this tutorial, you will learn how to use MySQL HAVING clause to specify a filter condition for groups of rows or aggregates.
Introduction to MySQL HAVING clause
The HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates.
The HAVING clause is often used with the GROUP BY clause to filter groups based on a specified condition. If the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE clause.
Notice that the HAVING clause applies a filter condition to each group of rows, while the WHERE clause applies the filter condition to each individual row.
MySQL HAVING clause examples
Let’s take some examples of using the HAVING clause to see how it works. We will use the orderdetailstable in the sample database for the demonstration.
You can use GROUP BY clause to get order numbers, the number of items sold per order, and total sales for each:
|
1
2
3
4
5
6
7
|
SELECT
ordernumber,
SUM(quantityOrdered) AS itemsCount,
SUM(priceeach*quantityOrdered) AS total
FROM
orderdetails
GROUP BY ordernumber;
|
Now, you can find which order has total sales greater than 1000 by using the HAVING clause as follows:
|
1
2
3
4
5
6
7
8
|
SELECT
ordernumber,
SUM(quantityOrdered) AS itemsCount,
SUM(priceeach*quantityOrdered) AS total
FROM
orderdetails
GROUP BY ordernumber
HAVING total > 1000;
|
You can construct a complex condition in the HAVING clause using logical operators such as OR and AND. Suppose you want to find which orders have total sales greater than 1000 and contain more than 600 items, you can use the following query:
|
1
2
3
4
5
6
7
8
|
SELECT
ordernumber,
SUM(quantityOrdered) AS itemsCount,
SUM(priceeach*quantityOrdered) AS total
FROM
orderdetails
GROUP BY ordernumber
HAVING total > 1000 AND itemsCount > 600;
|
Suppose you want to find all orders that have shipped and total sales greater than 1500, you can join the orderdetails table with the orders table using the INNER JOIN clause and apply a condition on statuscolumn and total aggregate as shown in the following query:
|
1
2
3
4
5
6
7
8
|
SELECT
a.ordernumber, status, SUM(priceeach*quantityOrdered) total
FROM
orderdetails a
INNER JOIN
orders b ON b.ordernumber = a.ordernumber
GROUP BY ordernumber, status
HAVING status = 'Shipped' AND total > 1500;
|
The HAVING clause is only useful when you use it with the GROUP BY clause to generate the output of the high-level reports. For example, you can use the HAVING clause to answer statistical questions like finding the number orders this month, this quarter, or this year that have total sales greater than 10K.
In this tutorial, you have learned how to use the MySQL HAVING clause with the GROUP BY clause to specify filter conditions for groups of rows or aggregates.
