题解 | #返回顾客名称和相关订单号以及每个订单的总价#
返回顾客名称和相关订单号以及每个订单的总价
https://www.nowcoder.com/practice/4dda66e385c443d8a11570a70807d250
解法一：
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
