sql - 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

在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)