题目：

订单中有多个商品，想要查询订单表(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;

附：
题目来源：https://mp.weixin.qq.com/s/2yq6TjjGnKVaqg6o3AWJtA