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

Question

我有一个数据库，存储“市场上有售”的产品和“仍在开发中”的产品 位于两个单独的表中（market_product 和 dev_product）。第三个表（物质）包含所有 产品可由哪些物质制成。其他两个表（marked_product_comp 和 dev_product_comp） 保持产品成分。

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

在以下（简化）示例中，查询必须从 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);

如何编写这样的查询？

---

更新：

抱歉，我没有注意到我以含糊的方式提出了我的问题。

我想选择仍在开发中且与至少一种已上市产品具有相同成分的产品。例如，如果有一种由物质 {1,2} 制成的 dev_product 并且只有一种由物质 {1,2,3} 制成的 market_product，我想丢弃它dev_product，因为它具有不同的成分。我希望这能澄清。

Answer 1

下面的解决方案依赖于 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 数据库。

Answer 2

在 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(*)：只要找到一个不匹配的组件即可停止评估整个对。

请参阅我的博客中的这些条目以获取说明：

• 匹配整个集合（PostgreSQL，带有完全外部连接）
• MySQL：匹配整个集合（MySQL，带有EXISTS）

Answer 3

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 4

仅选择所有产品物质均用于市场产品的开发产品 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)

不包括含有未在生产中使用的任何成分的产品。