*题解 | #确定最佳顾客的另一种方式（二）# 直接表联合

# 【问题】编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）。
# 提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法。

# 使用innerjoin
select cust_name,
sum(item_price*quantity) as total_price

from  OrderItems a 
inner join Orders b on a.order_num=b.order_num 
inner join Customers c on b.cust_id =c.cust_id
group by cust_name #因为select中的非聚合函数要在groupby出现
having total_price>=1000
order by total_price;

# 使用where 
select cust_name,
sum(item_price*quantity) as total_price

from  OrderItems a ,Orders b ,Customers c
where a.order_num=b.order_num and  b.cust_id =c.cust_id
group by cust_name #因为select中的非聚合函数要在groupby出现
having total_price>=1000
order by total_price