写一条 SQL 查询语句,从 Customer
表中查询购买了 Product
表中所有产品的客户的 id
思路:从 Customer
表中查询购买了 Product
表 ——> 两表联结,using(product_key)
Product
表中所有产品 —— > 在c表中查询同一个id都购买了p表中商品
顾客的id ——> distinct c.customer_id
select distinct customer_id
from customer as c
left join product as p
using(product_key)
where c.product_key = p.product_key
运行结果:会输入只买了6号产品的2id
查看别人的代码如下:
select distinct customer_id
from customer
group by customer_id
having count(distinct product_key) = (select count(*) as counts from product)
没有使用联结;对customer_id进行分组,之后过滤筛选出与去重的product_key合计数相等的所有数 —————俺看不懂
又找了一个代码,和上面那个差不多,但是我可以理解了。
select distinct customer_id
from customer
group by customer_id
having count(distinct product_key) = (select count(product_key) from product)