题解 | #返回每个顾客不同订单的总金额#

# 方法一
# 常规的join连接查询

select
    o.cust_id,
    sum(oi.item_price * oi.quantity) as total_ordered
from
    Orders o
join
    OrderItems oi
on
    o.order_num = oi.order_num
group by
    o.cust_id
order by
    total_ordered desc

-----------------------------------------

# 方法二
# join和子查询连用
# 本题特点，要查询的两个字段各自在两个表内，且没有筛选

select
    o.cust_id,
    sum(new_O.total_price) as total_ordered
from
    Orders o
join
    (select
        order_num,
        sum(item_price * quantity) as total_price
    from
        OrderItems
    group by # 1.先分组求出每个支付订单的总价
        1) as new_O
on
    o.order_num = new_O.order_num
group by
    o.cust_id # 2.再分组求出每个顾客的支付订单的总价
order by
    total_ordered desc