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

select
    cust_id,
    sum(
        (
            select
                SUM(item_price * quantity)
            from
                OrderItems
            group by
                order_num
            having
                OrderItems.order_num = Orders.order_num
        )
    ) as total_ordered
from
    Orders
group by
    cust_id
order by
    total_ordered desc

我的思路（为了方便描述，以下采用简单的名字）：

1.计算OrderItems表中：每个order_num 的总金额 = item * quantity=(结果可以在脑子里取个名字叫做total),根据order_num分组

然后，你可以将这个total加入到Orders表中，此时Orders变成了有三个字段：order_num, cust_id, total

对应的sql

 select SUM(item_price * quantity)
            from
                OrderItems
            group by
                order_num
// 上面这段代码起个名字叫做total

having语句是查询条件，两个表中都有的order_num，才进行计算total

注意没有ｉｎ，无法筛选，所以要增加ｈａｖｉｎｇ的条件

2.计算每个cust_id 对应的order_num 的总金额 的总额 = sum(total), 根据cust_id分组

因为每个cust_id 对应多个order_num

select cust_id, sum(total) as total_ordered
from orders
group by cust_id

3.两段代码合起来，最后再加一个排序