mysql - 获取已注册所有广告的 id

Question

我在查询中使用了 3 种类型的冒险。
只有 1 位客户预订了所有类型的冒险事件。
我用来获取数据的查询是:

    select c.customerid,c.name
    from customer c 
        inner join booking b
            on c.customerid = b.customerid 
        inner join destination d
            on b.destinationid=d.destinationid
        inner join adventure a
            on d.adventureid=a.adventureid
    group by c.customerid
    having count(distinct b.bid)=(select count(*) from bid)
        or count(distinct a.adventuretype)=(
            select count(distinct a.adventuretype)
            from adventure
        )

Answer 1

您可以使用聚合和 having 获取客户 ID。 :

select b.customerid
from booking b join
     destination d
     on b.destinationid = d.destinationid join
     adventure a
     on d.adventureid = a.adventureid
group by b.customerid
having count(distinct a.advtype) = 3;

或者，如果您不想对“3”进行硬编码，则可以使用:

having count(distinct a.advtype) = (select count(distinct advtype from adventure)

我会让您自行添加客户名称(使用 join 、 exists 或 in )。