题目：
订单中有多个商品，想要查询订单表(Orders)中同时包含有商品A、商品B、商品C等的所有订单信息。image
image
该如何写这个查询？
参考答案：
数据库版本：Server version: 8.0.20 MySQL Community Server - GPL
建表语句
create table dailytest_20200701(
order_id int,
product varchar(20));
数据准备
insert into dailytest_20200701 values (1001,'A'),(1001,'B'),(1001,'C'),(1001,'D'),(1002,'A');
查询逻辑-方法1
select
A.order_id
from (select order_id from dailytest_20200701 where product = 'A') A,
(select order_id from dailytest_20200701 where product = 'B') B,
(select order_id from dailytest_20200701 where product = 'C') C
where A.order_id = B.order_id
and B.order_id = C.order_id;
查询逻辑-方法2
select
AA.order_id,
AA.product
from dailytest_20200701 AA
where exists(select 1 from dailytest_20200701 A where A.order_id = AA.order_id and A.product = 'A')
and exists(select 1 from dailytest_20200701 B where B.order_id = AA.order_id and B.product = 'B')
and exists(select 1 from dailytest_20200701 C where C.order_id = AA.order_id and C.product = 'C')
and product in ('A','B','C');
查询逻辑-方法3
select
order_id
from dailytest_20200701
where product in ('A','B','C')
group by order_id
having count(distinct product) =3;
