MySQL多供应商订单匹配：找出可联合满足订单的两组供应商

我来帮你搞定这个找互补供应商组合的问题！咱们的目标是找出所有两两供应商组合，他们的产品集合加起来能完全覆盖订单要求的10、20、30、40、50这五个产品。下面分两种结果格式给你对应的SQL方案：

方案1：聚合版结果（匹配你第一个期望格式） #

这种方式会把每个供应商的拥有产品、缺失产品，以及能填补所有缺失的供应商候选都聚合在一行里：

-- 先定义CTE，生成每个供应商的拥有产品和缺失产品列表
WITH vendor_products AS (
    SELECT 
        vendor_id,
        STRING_AGG(product_id::TEXT, ', ') AS owned_products
    FROM vendors
    WHERE product_id IN (10, 20, 30, 40, 50)
    GROUP BY vendor_id
),
vendor_missing AS (
    SELECT 
        v.vendor_id,
        STRING_AGG(p.product_id::TEXT, ', ') AS missing_products,
        COUNT(p.product_id) AS missing_count
    FROM (SELECT DISTINCT vendor_id FROM vendors WHERE product_id IN (10,20,30,40,50)) v
    -- 生成订单要求的所有产品列表
    CROSS JOIN (SELECT UNNEST(ARRAY[10,20,30,40,50]) AS product_id) p
    LEFT JOIN vendors vd ON v.vendor_id = vd.vendor_id AND p.product_id = vd.product_id
    WHERE vd.product_id IS NULL
    GROUP BY v.vendor_id
)
-- 关联查询找出互补的供应商组合
SELECT 
    v1.vendor_id AS vendor_1,
    v1.owned_products,
    v1.missing_products,
    STRING_AGG(DISTINCT v2.vendor_id::TEXT, ', ') AS vendor_2_candidates
FROM vendor_products v1
JOIN vendor_missing vm1 ON v1.vendor_id = vm1.vendor_id
-- 关联拥有供应商1缺失产品的供应商
JOIN vendors v2_prod ON v2_prod.product_id IN (SELECT UNNEST(STRING_TO_ARRAY(vm1.missing_products, ', '))::INT)
JOIN vendor_products v2 ON v2.vendor_id = v2_prod.vendor_id
WHERE v1.vendor_id != v2.vendor_id
GROUP BY v1.vendor_id, v1.owned_products, v1.missing_products, vm1.missing_count
-- 确保供应商2拥有供应商1所有的缺失产品
HAVING COUNT(DISTINCT v2_prod.product_id) = vm1.missing_count
-- 避免重复组合（比如1234+9876和9876+1234只显示一次）
AND v1.vendor_id < v2.vendor_id;

这个查询会输出类似你第一个期望的结果，比如：

方案2：逐行明细版结果（匹配你第二个期望格式） #

如果需要更细粒度的展示，比如每个缺失产品对应的填补供应商，可以用这个方案：

-- 生成每个供应商的缺失产品明细
WITH vendor_missing AS (
    SELECT 
        v.vendor_id,
        p.product_id AS missing_product_id
    FROM (SELECT DISTINCT vendor_id FROM vendors WHERE product_id IN (10,20,30,40,50)) v
    CROSS JOIN (SELECT UNNEST(ARRAY[10,20,30,40,50]) AS product_id) p
    LEFT JOIN vendors vd ON v.vendor_id = vd.vendor_id AND p.product_id = vd.product_id
    WHERE vd.product_id IS NULL
)
-- 关联查询，展示每个供应商的拥有产品和缺失产品对应的填补供应商
SELECT 
    vd.vendor_id AS vendor_1,
    vd.product_id AS owned_product_id,
    vm.missing_product_id,
    v2.vendor_id AS vendor_2
FROM vendors vd
JOIN vendor_missing vm ON vd.vendor_id = vm.vendor_id
JOIN vendors v2 ON v2.product_id = vm.missing_product_id
WHERE vd.vendor_id != v2.vendor_id
ORDER BY vd.vendor_id, vd.product_id;

这个查询会输出类似你第二个期望的结果，比如：

小提示 #

• 如果你已经有现成的vendor_missing_products表，可以直接替换掉CTE里的vendor_missing逻辑，直接关联你的现有表即可。
• 加上v1.vendor_id < v2.vendor_id是为了避免重复组合（比如A+B和B+A），如果不需要这个限制，直接去掉该条件就行。

内容的提问来源于stack exchange，提问作者Daniel Gurzi