列表

详情


SQL92. 确定哪些订单购买了 prod_id 为 BR01 的产品(一)

描述

OrderItems代表订单商品信息表,prod_id为产品id;Orders表代表订单表有cust_id代表顾客id和订单日期order_date
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 解法, 执行用时: 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