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

#子查询
select cust_name,Orders.order_num,(select sum(quantity*item_price) from OrderItems 
where OrderItems.order_num = Orders.order_num 
group by order_num) as  OrderTotal
from Customers,Orders
where Customers.cust_id=Orders.cust_id
order by cust_name
#where查询
select cust_name,Orders.order_num,OrderTotal
from Customers,Orders,(select order_num,sum(quantity*item_price) as OrderTotal from OrderItems group by order_num) as tt
where Customers.cust_id=Orders.cust_id and Orders.order_num=tt.order_num
order by cust_name
#自然连接
select cust_name,order_num,OrderTotal
from Customers natural join Orders natural join(select order_num,sum(quantity*item_price) as OrderTotal from OrderItems group by order_num) as tt
order by cust_name
#内部连接
select cust_name,Orders.order_num,OrderTotal
from Customers inner join Orders on  Customers.cust_id=Orders.cust_id inner join(select order_num,sum(quantity*item_price) as OrderTotal from OrderItems group by order_num) as tt on Orders.order_num=tt.order_num
order by cust_name
#左连接
select cust_name,Orders.order_num,OrderTotal
from Customers left join Orders on  Customers.cust_id=Orders.cust_id left join(select order_num,sum(quantity*item_price) as OrderTotal from OrderItems group by order_num) as tt on Orders.order_num=tt.order_num
order by cust_name
#右连接
select cust_name,Orders.order_num,OrderTotal
from Customers right join Orders on  Customers.cust_id=Orders.cust_id right join(select order_num,sum(quantity*item_price) as OrderTotal from OrderItems group by order_num) as tt on Orders.order_num=tt.order_num
order by cust_name