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
总而言之答案正确,夸夸自己,再接再厉 : )