SQL92. 确定哪些订单购买了 prod_id 为 BR01 的产品(一)
描述
prod_id | order_num |
BR01 | a0001 |
BR01 | a0002 |
BR02 | a0003 |
BR02 | a0013 |
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 |
cust_id | order_date |
cust10 | 2022-01-01 00:00:00 |
cust1 | 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 解法, 执行用时: 38ms, 内存消耗: 6500KB, 提交时间: 2022-03-06
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, 内存消耗: 6464KB, 提交时间: 2022-03-03
select cust_id, order_date from OrderItems O join Orders D on O.order_num = D.order_num where O.prod_id = 'BR01'
Mysql 解法, 执行用时: 39ms, 内存消耗: 6472KB, 提交时间: 2022-03-04
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, 内存消耗: 6512KB, 提交时间: 2022-03-03
SELECT o.cust_id ,o.order_date FROM Orders o INNER JOIN OrderItems oi ON o.order_num=oi.order_num WHERE oi.prod_id='BR01' ORDER BY o.order_date
Mysql 解法, 执行用时: 40ms, 内存消耗: 6432KB, 提交时间: 2022-03-05
select cust_id, order_date from OrderItems, Orders where OrderItems.order_num = Orders.order_num and OrderItems.prod_id = 'BR01' order by order_date