題目：

image

image

該如何寫這個查詢？

參考答案：

數(shù)據(jù)庫版本：Server version: 8.0.20 MySQL Community Server - GPL

建表語句

create table dailytest_20200701(
    order_id int,
    product varchar(20));

數(shù)據(jù)準(zhǔn)備

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