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

WITH total_order_price AS(
    SELECT
      order_num,
      SUM(item_price * quantity) total_ordered
    FROM
      OrderItems
    GROUP BY
      order_num
  )
SELECT
  o.cust_id,
  tp.total_ordered
FROM
  Orders o
  LEFT JOIN total_order_price tp ON o.order_num = tp.order_num
ORDER BY
  tp.total_ordered DESC;

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

SELECT
  cust_id,
  (SELECT
      SUM(item_price * quantity) 
    FROM
      OrderItems a
    WHERE a.order_num = b.order_num) total_ordered  #聚合的字段放在select后,用where+关联条件关联即可
FROM
  Orders b
ORDER BY
  total_ordered DESC;