求个商家和商品对应关系的算法

Question

数据库中有商家对应销售的商品数据，格式如下：
商家1 001
商家1 002
商家1 003
商家2 001
商家2 002
商家3 001
......

怎么找出 既卖001又卖002的商家...

如果有N条数据呢？

如果知道要找的多个商品ID，如何用sql得出结果呢？

Answer 1

此处假定，商家-商品　的关联在该表中是唯一的。
下述代码是伪代码，提供思路

SELECT seller
FROM (SELECT
        seller,
        count(product) AS product_count
      FROM seller_product
      WHERE product IN (001, 002)
      GROUP BY seller) AS seller_list
WHERE product_count = 2

Answer 2

尝试写了一下，找出既卖001又卖002的商家ID

SELECT DISTINCT g1.shopid FROM goods g1, goods g2 WHERE g1.shopid = g2.shopid AND g1.goodsid = '001' AND g2.goodsid = '002'

或者多个

SELECT shopid FROM goods WHERE goodsid in (1,2,3,4) GROUP BY shopid HAVING( COUNT(DISTINCT goodsid) >= 4 );

Answer 3

SELECT seller.id,GROUP_CONCAT(goods.id) as goods_id FROM seller 
LEFT JOIN goods ON(goods.id=seller.id)
GROUP BY seller.id
HAVING FIND_IN_SET(001,goods_id) AND FIND_IN_SET(002,goods_id)

通过select获取商家id与商品id的关联数据。然后通过find_in_set过滤掉必须含有两个商品id的数据，就可以了。

Answer 4

感谢回答，不知道还有没有别的思路