SQL关联表时如何确保订单恰好包含样品的全部对应产品？

要实现"找出恰好包含样品全部产品且无额外产品的订单中最常用的样品"，核心是要同时满足订单产品集合与样品产品集合完全一致，可以通过以下步骤实现：

1. 预处理订单和样品的产品数量 #

先分别统计每个订单的产品总数、每个样品的产品总数，这是后续判断"无额外产品"的基础：

-- 统计每个订单的产品数量
WITH order_product_counts AS (
    SELECT order_id, COUNT(DISTINCT product_id) AS product_count
    FROM order_products
    GROUP BY order_id
),
-- 统计每个样品的产品数量
sample_product_counts AS (
    SELECT sample_id, COUNT(DISTINCT product_id) AS product_count
    FROM sample_products
    GROUP BY sample_id
)

2. 匹配符合条件的订单与样品 #

通过关联筛选，确保订单包含样品的所有产品，且两者产品数量一致（保证无额外产品）：

, matched_sample_orders AS (
    SELECT 
        s.id AS sample_id,
        s.name AS sample_name,
        o.id AS order_id
    FROM samples s
    JOIN sample_product_counts spc ON s.id = spc.sample_id
    -- 关联订单，先确保订单产品数和样品一致
    JOIN order_product_counts opc ON spc.product_count = opc.product_count
    JOIN orders o ON opc.order_id = o.id
    -- 检查订单是否包含样品的所有产品
    WHERE NOT EXISTS (
        SELECT 1
        FROM sample_products sp
        WHERE sp.sample_id = s.id
        AND NOT EXISTS (
            SELECT 1
            FROM order_products op
            WHERE op.order_id = o.id
            AND op.product_id = sp.product_id
        )
    )
)

3. 统计最常用的样品 #

最后统计每个样品匹配到的订单数，按数量倒序排序，取最常用的：

SELECT 
    sample_id,
    sample_name,
    COUNT(order_id) AS order_count
FROM matched_sample_orders
GROUP BY sample_id, sample_name
ORDER BY order_count DESC
LIMIT 1; -- 如果有多个样品订单数相同，可去掉LIMIT查看全部

完整SQL代码 #

把以上部分整合起来：

WITH order_product_counts AS (
    SELECT order_id, COUNT(DISTINCT product_id) AS product_count
    FROM order_products
    GROUP BY order_id
),
sample_product_counts AS (
    SELECT sample_id, COUNT(DISTINCT product_id) AS product_count
    FROM sample_products
    GROUP BY sample_id
),
matched_sample_orders AS (
    SELECT 
        s.id AS sample_id,
        s.name AS sample_name,
        o.id AS order_id
    FROM samples s
    JOIN sample_product_counts spc ON s.id = spc.sample_id
    JOIN order_product_counts opc ON spc.product_count = opc.product_count
    JOIN orders o ON opc.order_id = o.id
    WHERE NOT EXISTS (
        SELECT 1
        FROM sample_products sp
        WHERE sp.sample_id = s.id
        AND NOT EXISTS (
            SELECT 1
            FROM order_products op
            WHERE op.order_id = o.id
            AND op.product_id = sp.product_id
        )
    )
)
SELECT 
    sample_id,
    sample_name,
    COUNT(order_id) AS order_count
FROM matched_sample_orders
GROUP BY sample_id, sample_name
ORDER BY order_count DESC
LIMIT 1;

关键逻辑说明 #

• 通过order_product_counts和sample_product_counts的数量匹配，先排除那些产品数和样品不一致的订单，减少后续筛选的计算量
• 双层NOT EXISTS用于确保样品的每一个产品都存在于订单中，结合前面的数量匹配，就保证了订单和样品的产品集合完全一致（不多不少）
• 最后通过分组统计订单数，得到最常用的样品

内容的提问来源于stack exchange，提问作者George Morris