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

问题: SQL97 返回顾客名称和相关订单号以及每个订单的总价

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

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

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

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

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

order_num	quantity	item_price
a1	1000	10
a2	200	10
a3	10	15
a4	25	50
a5	15	25
a7	7	7

【问题】

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

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

cust_name	order_num	OrderTotal
an	a5	375
andy	a1	10000
ben	a2	2000
hex	a7	49
tom	a4	1250
tony	a3	150

【示例解析】

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

解答

思路

根据题目要求，我们需要输出：顾客名称、订单号、每个订单的总价（OrderTotal）。根据这个输出，可以初步确定，我们需要三张表的联合查询。

首先让两张表之间相互关联。

1. 先考虑顾客信息表：Customers 、订单信息表：Orders 通过两张表的字段，我们可以通过字段cust_id将两张表关联起来，得到顾客名称。

2. 订单信息表：Orders以及商品信息表OrderItems。 我们可以通过字段order_num关联起来，算出每个订单的总价。

SQL

1. 计算每个订单的总价

select 
    order_num,
    sum(quantity*item_price) OrderTotal 
from 
    OrderItems 
group by 
    order_num;

2. 查出每个订单对应的用户id(cust_id)以及订单号(order_num)

select 
    o.cust_id,
    t.order_num,
    t.OrderTotal 
from 
    Orders o
join 
(select 
    order_num,
    sum(quantity*item_price) OrderTotal 
from 
    OrderItems 
group by 
    order_num) t
on 
    t.order_num = o.order_num;

3. 连接Customers, 然后利用cust_id,将cust_id替换成cust_name

select 
    c.cust_name, 
    t.order_num, 
    t.OrderTotal
from
    Orders o
    inner join (
        select
            order_num,
            sum(quantity * item_price) OrderTotal
        from 
            OrderItems
        group by 
            order_num
    ) t
    on 
        o.order_num = t.order_num
    inner join 
        Customers c
    on 
        c.cust_id = o.cust_id
    order by
        c.cust_name, t.order_num;