sql 多对多关联如何查询指定关联的组是否存在？

Question

有一个简单的多对多带数量的关联关系，举例：
水果表(Fruits:id, name)：
1 桃子
2 苹果
3 香蕉
...

篮子表(Bucket:id)：
1
2
3
...

水果篮子对应表(id, bucket_id, fruit_id, count)
比如：1号篮子里有两个苹果1个香蕉，
1 1 2 2
2 1 3 1

现在需求是查询有没有1个篮子里只有2个苹果和1个香蕉，如果有就返回他的id，没有就新建
新建的逻辑先不管，怎么判断存在呢

Answer 1

个人思路：先找出有苹果=2、香蕉=1的篮子，再排除掉有其他水果的篮子，得到最终结果

sql如下：

select t3.bucket_id from 
(
  select t1.bucket_id from
  (select bucket_id from 对应表 where fruit_id = 2 and count = 2) t1
  inner join 
  (select bucket_id from 对应表 where fruit_id = 3 and count = 1) t2
  on t1.bucket_id = t2.bucket_id
) t3,
(
  select bucket_id
  from 对应表
  group by bucket_id
  having count(1) = 2
) t4
where t3.bucket_id = t4.bucket_id

该sql只是为了满足基础需求，只能在数据量不大的情况下使用，否则肯定会有性能问题

Answer 2

stackoverflow上得到了一个感觉不错的答案..

select distinct bucket_id from 对应表 t
where exists (select * from 对应表 where bucket_id=t.bucket_id and fruit_id=2 and count=2)
and exists (select * from 对应表 where bucket_id=t.bucket_id and fruit_id=3 and count=1)
and bucket_id in (select bucket_id from 对应表 group by bucket_id having count(1)=2);

Answer 3

如果不考虑性能的情况下可以这样做：

• 用bucket_id对水果篮子对应表进行自连接形成表temp
• 对temp进行查询，返回1个篮子里只有2个苹果和1个香蕉的记录

伪代码

with temp as 
    (select * from 水果篮子对应表 t1 cross join 水果篮子对应表 t2 where t1.bucket_id = t2.bucket_id  )
select * from temp where temp.苹果=2 and 香蕉=1 and 其它=0