SQL94 返回每个顾客不同订单的总金额

返回每个顾客不同订单的总金额_牛客题霸_牛客网 (nowcoder.com)https://www.nowcoder.com/practice/ce313253a81c4947b20e801cd4da7894?tpId=298&tqId=2374691&ru=/exam/oj&qru=/ta/sql-teach-yourself/question-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D298

首先根据OrderItems表可以计算每个order_num订购的总金额，题目让求每个顾客的订单总数 

每个顾客可能有好几个 order_num，我们要求这些的和，怎么办？

join就好

select 
    cust_id,
    sum(total) as total_ordered
from Orders 
join (select 
        order_num,
        sum(item_price*quantity) as total
from OrderItems
group by order_num) oi using(order_num)
group by cust_id
having total_ordered
order by total_ordered desc

还有一种写法 select使用自连接

select
  cust_id,
  (
    select
      SUM(item_price * quantity)
    FROM
      OrderItems a
    WHERE
      a.order_num = b.order_num
  ) total_ordered
from
  Orders b
ORDER BY total_ordered DESC

猜测，应该是，对b表中的每一个顾客，去看她的order_num，相等就计算，猜的，不知道对不对