SQL101. 检索每个顾客的名称和所有的订单号（一）
描述
| cust_id | cust_name |
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
| order_num | cust_id |
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
| cust_name | order_num |
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
示例1
输入:
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');
输出:
an|a5 andy|a1 ben|a2 hex|a7 tom|a4 tony|a3
Mysql 解法, 执行用时: 39ms, 内存消耗: 6464KB, 提交时间: 2022-03-07
select a.cust_name cust_name, b.order_num order_num from Customers a , Orders b where a.cust_id = b.cust_id order by cust_name
Mysql 解法, 执行用时: 39ms, 内存消耗: 6476KB, 提交时间: 2022-03-06
SELECT cust_name,order_num FROM Customers JOIN Orders USING(cust_id) ORDER BY cust_name
Mysql 解法, 执行用时: 40ms, 内存消耗: 6428KB, 提交时间: 2022-06-15
select C.cust_name,O.order_num
from Customers C
inner join Orders O on O.cust_id=C.cust_id
order by C.cust_name
Mysql 解法, 执行用时: 40ms, 内存消耗: 6440KB, 提交时间: 2022-03-03
select Customers.cust_name,Orders.order_num
from Customers
inner join Orders on Customers.cust_id = Orders.cust_id order by cust_name;
Mysql 解法, 执行用时: 40ms, 内存消耗: 6444KB, 提交时间: 2022-08-05
select cust_name,order_num from Customers inner join Orders on Customers.cust_id=Orders.cust_id order by cust_name;