问题写法

按照上题简单的写法来写的话 这题可以写出如下的sql

select cust_name,sum(item_price*quantity) as total_price
from Orders as t1
inner join OrderItems as t2 on t1.order_num = t2.order_num
inner join Customers as t3 on t1.cust_id = t3.cust_id
group by cust_name
having total_price >= 1000
order by total_price

但是显然以cust_name来group是不妥当的，万一有两个同名的用户就gg了

改进

虽然麻烦了一点，但是正确性最起码有保障

select cust_name, total_price
from Customers
inner join
( 
    select cust_id, sum(item_price * quantity) as total_price
    from Orders
    inner join OrderItems on OrderItems.order_num = Orders.order_num
    group by cust_id
    having total_price >= 1000
) as res
on Customers.cust_id = res.cust_id
order by total_price