上节课,我们学习了按照客户去分类销售总额
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