检索每个顾客的名称和所有的订单号（二）

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'),('cust40','ace'); 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 解法, 执行用时: 38ms, 内存消耗: 6544KB, 提交时间: 2022-03-04

SELECT cust_name, order_num
FROM Customers LEFT JOIN Orders
USING(cust_id)
Order by cust_name

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

SELECT cust_name,order_num FROM Customers
LEFT JOIN Orders USING(cust_id)
ORDER BY cust_name

Mysql 解法, 执行用时: 39ms, 内存消耗: 6500KB, 提交时间: 2022-03-04

select cust_name,order_num
from Customers
left join Orders using(cust_id)
order by cust_name

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

select cust_name,order_num
from Orders a 
right join Customers b on a.cust_id=b.cust_id
order by cust_name;

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

select c.cust_name,o.order_num
from Customers c 
left JOIN Orders o using(cust_id) 
order by c.cust_name

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
cust40	ace

cust_name	order_num
ace	NULL
an	a5
andy	a1
ben	a2
hex	a7
tom	a4
tony	a3

详情