MySql |TheHAVING Clause

SELECT 
     client_id,
     SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id  -- 分组
HAVING total_sales > 500-- 使用having子句：为了在分组之后进行筛选

Output:

 

-------------------------------------------------作业--------------------------------------------------

找出 位于 virginia，  花费超过100元的  顾客：

solution：

SELECT 
        c.customer_id,
        c.first_name,
		c.last_name,
        SUM(oi.quantity*oi.unit_price) AS total_sales
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_sales > 100

output: