Orders表代表订单信息含有订单号order_num和顾客id cust_id
order_num | cust_id |
a1 | cust10 |
a2 | cust1 |
a3 | cust2 |
a4 | cust22 |
a5 | cust221 |
a7 | cust2217 |
Customers表代表顾客信息含有顾客id cust_id和 顾客名称 cust_name
cust_id | cust_name |
cust10 | andy |
cust1 | ben |
cust2 | tony |
cust22 | tom |
cust221 | an |
cust2217 | hex |
cust40 | ace |
【问题】检索每个顾客的名称(Customers表中的 cust_name)和所有的订单号(Orders 表中的 order_num),列出所有的顾客,即使他们没有下过订单。最后根据顾客姓名cust_name升序返回。
【示例结果】
返回顾客名称cust_name和订单号order_num
cust_name | order_num |
ace | NULL |
an | a5 |
andy | a1 |
ben | a2 |
hex | a7 |
tom | a4 |
tony | a3 |
【示例解析】
基于两张表,返回订单号a1的顾客名称andy等人,没有下单的顾客ace也统计了进来
示例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'),('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');
输出:
ace|None an|a5 andy|a1 ben|a2 hex|a7 tom|a4 tony|a3
select
cust_name,
order_num
from
Customers
left join
Orders
on Customers.cust_id=Orders.cust_id
order by
cust_name;