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

解法一：

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   --1.先把表Customers和表Orders 拼起来命名为co表

on oi.order_num = co.order_num   --2.再把表OrderItems（别名oi）跟co表拼起来成为一张大表，在大表里按条件查询。

group by co.cust_name

order by co.cust_name, ANY_VALUE(oi.order_num)

解法二：

select c.cust_name, oito.order_num, oito.OrderTotal from Customers as c join ( select o.order_num, o.cust_id, oit.OrderTotal from Orders as o join (select o.order_num ,sum(o.quantity * o.item_price) OrderTotal from OrderItems as o group by o.order_num ) as oit --第一张表oit的列有 order_num，OrderTotal on o.order_num = oit.order_num ) as oito --第二张表oito的列有order_num，cust_id，OrderTotal on c.cust_id = oito.cust_id --第二张表取cust_id这个列就是因为第三张表需要cust_id列才关联 order by c.cust_name, oito.order_num --第三张表的列有cust_name，order_num，OrderTotal