美文网首页
SQL每日一题(2020-07-01) ***

SQL每日一题(2020-07-01) ***

作者: 扎西的德勒 | 来源:发表于2020-07-02 11:07 被阅读0次

    题目:

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

    相关文章

      网友评论

          本文标题:SQL每日一题(2020-07-01) ***

          本文链接:https://www.haomeiwen.com/subject/chblqktx.html