38.返回顾客名称和相关订单号以及每个订单的总价 Custo_

38.返回顾客名称和相关订单号以及每个订单的总价

Customers 表有字段，顾客名称：cust_name、顾客id：cust_id

Orders订单信息表，含有字段，订单号：order_num、顾客id：cust_id

OrderItems表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price

【问题】

除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。

【示例结果】返回顾客名称 cust_name、订单号order_num、订单总额OrderTotal

【示例解析】

例如顾客名称cust_name为an的顾客的订单a5的订单总额为quantity*item_price = 15 * 25 = 375，最后以cust_name和order_num来进行升序排序。

select cust_name,t3.order_num ,avg(quantity*item_price) as OrderTotal

from Customers as t1 inner join Orders as t2

on t1.cust_id=t2.cust_id

inner join OrderItems as t3

on t2.order_num=t3.order_num

group by cust_name,t3.order_num

order by cust_name,t3.order_num

select cust_name,t3.order_num ,sum(quantity*item_price) as OrderTotal

from Customers as t1 inner join Orders as t2

on t1.cust_id=t2.cust_id

inner join OrderItems as t3

on t2.order_num=t3.order_num

group by cust_name,t3.order_num

order by cust_name,t3.order_num

select cust_name,o.order_num,quantity*item_price as OrderTotal

from Customers as c join Orders as o join OrderItems as oi

on c.cust_id = o.cust_id and o.order_num = oi.order_num

order by cust_name,o.order_num;

38.返回顾客名称和相关订单号以及每个订单的总价 Custo