北京企业网站建设飞沐,网站开发公司照片,网页设计图片素材小插件,网站开发网络描述你想知道订购 BR01 产品的日期#xff0c;有表OrderItems代表订单商品信息表#xff0c;prod_id为产品id#xff1b;Orders表代表订单表有cust_id代表顾客id和订单日期order_date#xff1b;Customers表含有cust_email 顾客邮件和cust_id顾客idOrderItems表prod_idorde…描述你想知道订购 BR01 产品的日期有表OrderItems代表订单商品信息表prod_id为产品idOrders表代表订单表有cust_id代表顾客id和订单日期order_dateCustomers表含有cust_email 顾客邮件和cust_id顾客idOrderItems表prod_idorder_numBR01a0001BR01a0002BR02a0003BR02a0013Orders表order_numcust_idorder_datea0001cust102022-01-01 00:00:00a0002cust12022-01-01 00:01:00a0003cust12022-01-02 00:00:00a0013cust22022-01-01 00:20:00Customers表代表顾客信息cust_id为顾客idcust_email为顾客email【问题】返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件Customers 表中的 cust_email结果无需排序。提示这涉及 SELECT 语句最内层的从 OrderItems 表返回 order_num中间的从 Customers 表返回 cust_id。【示例结果】返回顾客email cust_emailcust_emailcust10cust.comcust1cust.com【示例解析】 产品id为BR01的订单a0001和a002的下单顾客cust10和cust1的顾客email cust_email分别是cust10cust.com 、cust1cust.com示例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);DROP TABLE IF EXISTS Customers;CREATE TABLE IF NOT EXISTS Customers( cust_id VARCHAR(255) NOT NULL COMMENT 顾客id, cust_email VARCHAR(255) NOT NULL COMMENT 顾客email );INSERT Customers VALUES (cust10,cust10cust.com),(cust1,cust1cust.com),(cust2,cust2cust.com);复制输出cust10cust.comcust1cust.com答案select t3.cust_email
from Orders t1,OrderItems t2,Customers t3
where t1.order_numt2.order_num
and t1.cust_idt3.cust_id
and t2.prod_idBR01总共有3张表将这3张表关联起来然后根据要求筛选即可。
