第五章 3-Having子句the having clause--Mosh my SQL

上节课，我们学习了按照客户去分类销售总额

1，这次我们只想筛选销售额大于500的客户，如果用where，因为此时还没有分组了数据，所以在第五行where的时候，还不知道每个客户的销售额。此时报错

Error Code: 1054. Unknown column 'total_sales' in 'where clause'

select
client_id,
sum(invoice_total) as total_sales
from invoices
where total_sales>500
group by client_id

2，因此我们用having clause：

select
client_id,
sum(invoice_total) as total_sales
from invoices
group by client_id
having total_sales>500

3，复合筛选发票数大于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 and number_of_invoices>5

having可以和where一样都有多个条件，但是用到的列一定要是select里面提到的

4，练习 用store数据库的内容

答案：

select
    c.customer_id,
    c.first_name,
    c.last_name,
    sum(oi.quantity*oi.unit_price)as total_order_payments
from customers c
join orders o using(customer_id)
join order_items oi using (order_id)
where state='VA'
group by
    c.customer_id,
    c.first_name,
    c.last_name
having total_order_payments>100

注意：一般在自带函数前面的从句，都可以搬运到group by之后作为分组依据；where，group by，having顺序依次递增。

复杂问题分成小块一一进行：

1，筛选在VA的顾客

2，join三张表

3，sum,group by ,having