题解 | #返回顾客名称和相关订单号以及每个订单的总价#

解法一：表3先联合表2做成新表，新表再联合表1查询。
select c.cust_name, co.order_num, co.OrderTotal
from Customers as c
join
    (
        select o.order_num, o.cust_id, oit.OrderTotal 
        from Orders as o
        join
            (
                select oi.order_num, sum(oi.quantity*oi.item_price) as OrderTotal
                from OrderItems as oi group by order_num
            ) as oit  -- 一、括号内是想从表OrderItems中查出order_num和OrderTotal字段，结果做成一个新表命名为oit；
        on o.order_num = oit.order_num
    ) as co -- 二、括号内是想根据共同列order_num联合表Orders与表oit，查出order_num，cust_id，OrderTotal字段，结果做成新表命名为co。
on c.cust_id = co.cust_id
order by c.cust_name, co.order_num -- 三、用表Customers根据共同列cust_id列联合新表co查出结果。


解法二：表1联合表2做成新表，新表再联合表3查询。
select co.cust_name, ANY_VALUE(oi.order_num), sum(oi.quantity*oi.item_price) as OrderTotal
from OrderItems as oi
join
    (
    select c.cust_id, c.cust_name, o.order_num
    from Customers as c
    join Orders as o
    on c.cust_id = o.cust_id
    ) as co
on oi.order_num = co.order_num
group by co.cust_name
order by co.cust_name, ANY_VALUE(oi.order_num)