确定哪些订单购买了 prod_id 为 BR01 的产品（二）

OrderItems表

prod_id	order_num
BR01	a0001
BR01	a0002
BR02	a0003
BR02	a0013

Orders表

order_num	cust_id	order_date
a0001	cust10	2022-01-01 00:00:00
a0002	cust1	2022-01-01 00:01:00
a0003	cust1	2022-01-02 00:00:00
a0013	cust2	2022-01-01 00:20:00

【问题】

编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。

提示：这一次使用联结和简单的等联结语法。

【示例结果】

返回顾客id cust_id和定单日期order_date

cust_id	order_date
cust10	2022-01-01 00:00:00
cust1	2022-01-01 00:01:00

【示例解析】

产品id为BR01的订单a0001和a002的下单顾客cust10和cust1的下单时间分别为2022-01-01 00:00:00和2022-01-01 00:01:00

示例1

输入:

DROP TABLE IF EXISTS `OrderItems`;
  CREATE TABLE IF NOT EXISTS `OrderItems`(
    prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
  );
  INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

  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',
    order_date TIMESTAMP NOT NULL COMMENT '下单时间'
  );
  INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');

输出:

cust10|2022-01-01 00:00:00
cust1|2022-01-01 00:01:00

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

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

select cust_id,order_date from Orders where order_num in (
    select order_num from OrderItems where prod_id='BR01'
)order by order_date

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

SELECT cust_id,order_date
FROM Orders LEFT JOIN OrderItems USING(order_num)
WHERE prod_id = 'BR01'
ORDER BY order_date

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

select cust_id , order_date from Orders,OrderItems
where Orders.order_num= OrderItems.order_num and OrderItems.prod_id = 'BR01'
order by order_date

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

SELECT cust_id ,order_date 
from(select * from OrderItems where prod_id='BR01') a join Orders b 
on a.order_num = b.order_num
order by order_date

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

SELECT b.cust_id,b.order_date
FROM OrderItems a,Orders b
WHERE a.order_num=b.order_num
AND a.prod_id='BR01'
ORDER BY b.order_date;

上一题

下一题

详情