MySQL HAVING

简介

HAVING 字句 主要用于经过Group by之后SELECT中的数据的筛选。

HAVING子句通常与GROUP BY子句一起使用，以根据指定条件过滤组。如果省略了GROUP BY子句，则HAVING子句的行为与WHERE子句类似

注意，HAVING子句将筛选条件应用于每一组行，而WHERE子句将筛选条件应用于每一行

MySQL HAVING 字句实例

有如下示例表：

You can use GROUP BY clause to get order numbers, the number of items sold per order, and total sales for each:

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:

SELECT 
    ordernumber,
    SUM(quantityOrdered) AS itemsCount,
    SUM(priceeach*quantityOrdered) AS total
FROM
    orderdetails
GROUP BY ordernumber
HAVING total > 1000;

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 status column and total aggregate as shown in the following query:

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;

HAVING子句仅仅用于当你用GROUP BY 字句来生成高级报告；

例如，您可以使用HAVING子句来回答统计问题，比如查找本月、本季度或今年的总销售额超过10K的订单数量。

参考