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

根据实际情况，可以知道顾客和订单号是一对多的关系，那么，同一订单号应当是对应一位顾客ID，故可以先在OrderItems表中按订单号分组用SUM()函数计算出同一订单的总金额:

SELECT order_num, SUM(item_price*quantity) AS total_ordered
FROM OrderItems
GROUP BY order_num

可以根据以上用WITH...AS创建新表total_order_price，然后通过键order_num将其与Orders表相连，完整代码如下：

WITH total_order_price AS (
    SELECT order_num, SUM(item_price*quantity) AS total_ordered
    FROM OrderItems
    GROUP BY order_num
)
SELECT cust_id, total_ordered
FROM Orders 
    LEFT JOIN 
    total_order_price
    USING(order_num)
ORDER BY total_ordered DESC