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

DROP TABLE IF EXISTS `OrderItems`; CREATE TABLE IF NOT EXISTS `OrderItems`( order_num VARCHAR(255) NOT NULL COMMENT '商品订单号', item_price INT(16) NOT NULL COMMENT '售出价格', quantity INT(16) NOT NULL COMMENT '商品数量' ); INSERT `OrderItems` VALUES ('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5); DROP TABLE IF EXISTS `Customers`; CREATE TABLE IF NOT EXISTS `Customers`( cust_id VARCHAR(255) NOT NULL COMMENT '客户id', cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名' ); INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex'); DROP TABLE IF EXISTS `Orders`; CREATE TABLE IF NOT EXISTS `Orders`( order_num VARCHAR(255) NOT NULL COMMENT '商品订单号', cust_id VARCHAR(255) NOT NULL COMMENT '顾客id' ); INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

Mysql 解法, 执行用时: 40ms, 内存消耗: 6444KB, 提交时间: 2022-03-07

SELECT C.cust_name,SUM(A.item_price *A.quantity)AS total_price FROM OrderItems AS A
INNER JOIN Orders AS B ON A.order_num=B.order_num
INNER JOIN Customers AS C ON B.cust_id=C.cust_id
GROUP BY C.cust_name
HAVING SUM(A.item_price *A.quantity) >= 1000
ORDER BY C.cust_name

Mysql 解法, 执行用时: 40ms, 内存消耗: 6516KB, 提交时间: 2022-06-18

SELECT t1.cust_name, SUM(t3.item_price*t3.quantity) total_price
FROM Customers t1
JOIN Orders t2 USING (cust_id)
JOIN OrderItems t3 USING (order_num)
GROUP BY t1.cust_name
HAVING total_price >= 1000
ORDER BY total_price

Mysql 解法, 执行用时: 41ms, 内存消耗: 6440KB, 提交时间: 2022-06-18

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

Mysql 解法, 执行用时: 41ms, 内存消耗: 6444KB, 提交时间: 2022-08-05

select cust_name,sum(item_price*quantity) total_price
from Customers c join Orders o1 join OrderItems o2
on c.cust_id = o1.cust_id
and o1.order_num = o2.order_num
group by cust_name
having total_price>=1000;

Mysql 解法, 执行用时: 41ms, 内存消耗: 6444KB, 提交时间: 2022-08-05

SELECT 
    cust_name, 
    SUM(item_price * quantity) AS total_price
FROM 
    Customers
JOIN 
    Orders USING (cust_id)
JOIN 
    OrderItems USING (order_num)
GROUP BY 
    cust_name
HAVING
    SUM(item_price * quantity) >= 1000
ORDER BY 
    total_price

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

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

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

cust_name	total_price
andy	1050
ben	1319
tom	2242

详情