SQL100 确定最佳顾客的另一种方式（二）

描述

OrderItems表代表订单信息，确定最佳顾客的另一种方式是看他们花了多少钱，OrderItems表有订单号order_num和item_price商品售出价格、quantity商品数量

order_num	item_price	quantity
a1	10	105
a2	1	1100
a2	1	200
a4	2	1121
a5	5	10
a2	1	19
a7	7	5

Orders表含有字段order_num 订单号、cust_id顾客id

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

顾客表Customers有字段cust_id 客户id、cust_name 客户姓名

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

【问题】编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）。

提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法。

【示例结果】

cust_name	total_price
andy	1050
ben	1319
tom	2242

【示例解析】

总额（item_price 乘以 quantity）大于等于1000的订单号，例如a2对应的顾客id为cust1，cust1的顾客名称cust_name是ben，最后返回ben作为order_num a2的quantity * item_price总和的结果1319。

解答 1

select c.cust_name, t.total_price
from
    Customers c
    inner join Orders o
    on c.cust_id = o.cust_id
    inner join (
        select order_num, sum(item_price*quantity) total_price
        from OrderItems
        group by order_num
        having total_price >= 1000
    ) t
    on t.order_num = o.order_num
order by t.total_price;

解答 2

select
    cust_name, 
    sum(quantity*item_price) total_price
from 
    OrderItems oi
inner join 
    Orders o
on 
    o.order_num = oi.order_num
inner join
    Customers c
on 
    c.cust_id = o.cust_id
group by 
    cust_name
having
    sum(quantity*item_price) >= 1000
order by 
    sum(quantity*item_price);