The Having Clause | Having子句 | MySQL

The having clause

情景一

给出一个情景

目前已知查询如上，现在如果只想查询包含总销售高于500美金的客户，如何处理？（即在这个表中，我们不想返回client_id=2）

思考问题：
一般都会想到使用WHERE，即

SELECT
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
WHERE total_sales > 500
GROUP BY client_id

但执行起来会报错

SELECT  client_id,     SUM(invoice_total) AS total_sales FROM invoices WHERE total_sales > 500 GROUP BY client_id LIMIT 0, 1000

这是因为当出现WHERE条件句时，我们还没有进行分组
且WHERE中是不能使用聚合函数的

因此这里需要having子句，帮助我们在分组之后进行数据的筛选
正确的解决方案：

SELECT
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
-- WHERE total_sales > 500
GROUP BY client_id
HAVING total_sales > 500

可以这么理解：
WHERE可以在分组之前筛选数据
HAVING可以在分组之后筛选数据

情景二

给出一个情景
在上述解决方案基础上，返回一下发票数量，这里可以用上COUNT

SELECT
	client_id,
    SUM(invoice_total) AS total_sales,
    count(*) AS number_of_invoices
FROM invoices
-- WHERE total_sales > 500
GROUP BY client_id
HAVING total_sales > 500

在此基础上你想要返回发票数量超过5的记录
可以利用having子句写一个复合搜索条件

HAVING total_sales > 500 AND number_of_invoices > 5

完整即

SELECT
	client_id,
    SUM(invoice_total) AS total_sales,
    count(*) AS number_of_invoices
FROM invoices
GROUP BY client_id
-- HAVING total_sales > 500
HAVING total_sales > 500 AND number_of_invoices > 5

注意：

• HAVING子句中涉及的列需要是在SELECT语句中的
• 但作为对比，WHERE可以使用任何列，无论有没有出现在SELECT中

练习

1-使用到SQL store数据库
2-编写一个查询，得到坐标（located）在Virginia并且消费超过100美金的顾客（custoners）

我的答案

SELECT 
	c.customer_id ,
    c.first_name  ,
    c.last_name  ,
    c.state,
    SUM(oi.quantity * oi.unit_price)  AS payments
FROM 
	customers c
JOIN
	orders o
ON
	c.customer_id = o.customer_id
JOIN
	order_items oi
ON
	o.order_id = oi.order_id
GROUP BY customer_id,c.first_name,c.last_name,c.city
HAVING c.state = 'VA' AND payments > 100

上述连接可以表达的更方便 使用USING语句

SELECT 
	c.customer_id ,
    c.first_name  ,
    c.last_name  ,
    c.state,
    SUM(oi.quantity * oi.unit_price)  AS payments
FROM 
	customers c
JOIN orders o USING (customer_id)
-- ON c.customer_id = o.customer_id
JOIN order_items oi USING (order_id)
-- ON o.order_id = oi.order_id
GROUP BY customer_id,c.first_name,c.last_name,c.city
HAVING c.state = 'VA' AND payments > 100

总而言之答案正确，夸夸自己，再接再厉 : ）