SQL41 确定最佳顾客的另一种方式（二）

OrderItems 表

• order_num：订单号
• item_price：商品单价
• quantity：商品数量

order_num	item_price	quantity
a1	10	105
a2	1	1100
a2	1	200
a4	2	1121
a5	5	10
a2	1	19
a7	7	5

Orders 表

• order_num：订单号
• cust_id：顾客 id

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

Customers

• cust_id：顾客 id
• cust_name：顾客姓名

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

问题

编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额

示例结果

cust_name	total_price
andy	1050
ben	1319
tom	2242

分析

第一步：
在OrderItems表中找出总额不小于1000的订单号

select
	order_num,
	sum(item_price * quantity) total_price
from OrderItems
group by order_num
having total_price >= 1000

第二步：
将Customers表与Orders表进行内连接，之后再与第一步得到的新表进行内连接

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

牛客网：SQL41 确定最佳顾客的另一种方式（二）