用于选择具有其他产品的相同成分的产品的SQL查询

Question

我有一个数据库，可以在两个单独的表（market_product和dev_product）中存储“市场上可用”产品和“仍在开发中”产品 。第三个表（物质）包含产品可以制成的所有物质。其他两个表（marked_product_comp和dev_product_comp） mantains产品组成。

我想选择仍在开发中的产品是由相同成分的 市售产品制成的。

在以下（简化）示例中，查询必须从dev_product表中选择ID = 2的产品。

CREATE table market_product (ID SERIAL PRIMARY KEY); 
CREATE table dev_product (ID SERIAL PRIMARY KEY); 
CREATE table substance (ID SERIAL PRIMARY KEY); 
CREATE table market_product_comp (prodID SERIAL, substID SERIAL, PRIMARY KEY(prodID,substID)); 
CREATE table dev_product_comp (devID SERIAL, substID SERIAL, PRIMARY KEY(devID,substID)); 

INSERT INTO market_product VALUES (1),(2); 
INSERT INTO dev_product VALUES (1),(2); 
INSERT INTO substance VALUES (1),(2),(3); 
INSERT INTO market_product_comp VALUES (1,1),(1,2),(2,3); 
INSERT INTO dev_product_comp VALUES (1,2),(2,1),(2,2); 

如何编写这样的查询？

---

UPDATE：

对不起，我没有注意到，我问我的问题暧昧的方式。

我想选择仍在开发中的产品具有至少一种市售产品的相同成分。例如，如果物质{1,2}生成的dev_product和物质{1,2,3}生成的只有一个market_product，我想放弃dev_product，因为它具有不同的组成。我希望澄清。

Answer 1

在MySQL：

SELECT * 
FROM dev_product dp 
WHERE EXISTS 
     (
     SELECT NULL 
     FROM market_product mp 
     WHERE NOT EXISTS 
       (
       SELECT NULL 
       FROM dev_product_comp dpc 
       WHERE dpc.prodID = dp.id 
         AND NOT EXISTS 
         (
         SELECT NULL 
         FROM market_product_comp mpc 
         WHERE mpc.prodID = mp.id 
           AND mpc.substID = dpc.substID 
         ) 
       ) 
       AND NOT EXISTS 
       (
       SELECT NULL 
       FROM market_product_comp mpc 
       WHERE mpc.prodID = mp.id 
         AND NOT EXISTS 
         (
         SELECT NULL 
         FROM dev_product_comp dpc 
         WHERE dpc.prodID = dp.id 
           AND dpc.substID = mpc.substID 
         ) 
       ) 

     ) 

在PostgreSQL：

SELECT * 
FROM dev_product dp 
WHERE EXISTS 
     (
     SELECT NULL 
     FROM market_product mp 
     WHERE NOT EXISTS 
      (
      SELECT NULL 
      FROM (
       SELECT substID 
       FROM market_product_comp mpc 
       WHERE mpc.prodID = mp.ID 
       ) m 
      FULL OUTER JOIN 
       (
       SELECT substID 
       FROM dev_product_comp dpc 
       WHERE dpc.devID = dp.ID 
       ) d 
      ON d.substID = m.substID 
      WHERE d.substID IS NULL OR m.substID IS NULL 
      ) 
     ) 

无论从这些查询使用COUNT(*)：它足以找到，但单一的非匹配组件停止评估全对。

见我的博客这些条目的解释：

• Matching whole sets（PostgreSQL，与FULL OUTER JOIN）
• MySQL: Matching whole sets（MySQL，与EXISTS）

Answer 2

select d.* from dev_product d 
left join dev_product_comp dpc on d.Id = dpc.devId 
where dpc.substID in 
    (select mpc.substID from market_product_comp mpc 
    left join market_product mp on mp.Id = mpc.prodId) 

Answer 3

只选择dev产品id，其中所有产品物质都用于市场产品。

select 
    dp.id 
from 
    dev_product dp 
    inner join dev_product_comp dpc on dp.id = dpc.devid 
where 
    dpc.substid in (select substid from market_product_comp) 
group by 
    dp.id 
having 
    count() = (select count() from dev_product_comp where devid = dp.id) 

不包括任何未用于生产的成分的产品。

Answer 4

下面是依赖于以下事实的解决方案COUNT()忽略NULL。

SELECT d1.devId, m1.prodId 
FROM market_product_comp m1 
CROSS JOIN dev_product_comp d1 
LEFT OUTER JOIN dev_product_comp d2 
    ON (d2.substId = m1.substId AND d1.devId = d2.devId) 
LEFT OUTER JOIN market_product_comp m2 
    ON (d1.substId = m2.substId AND m1.prodId = m2.prodId) 
GROUP BY d1.devId, m1.prodId 
HAVING COUNT(d1.substId) = COUNT(d2.substId) 
    AND COUNT(m1.substId) = COUNT(m2.substId); 

我在MySQL 5.0.75上测试了它，但它都是ANSI标准的SQL，所以它应该适用于任何品牌的SQL数据库。