时间戳

首页 > 解决方案 > MYSQL 匹配具有多个 id 的同一张表中的多行

mysql - MYSQL 匹配具有多个 id 的同一张表中的多行

问题描述

我正在研究一种处理订单的方法，我必须满足来自多个供应商的订单，并且我想知道哪两个供应商可以提供给定的订单。

我订单中的 product_ids 是 10,20,30,40,50

SELECT * 
  FROM vendors 
 WHERE product_id IN (10,20,30,40,50)

给我所有至少拥有 1 种产品的供应商

vendor_id | product_id 
1234          10
1234          20
1234          30
1234          40
1235          10
1235          40
1236          20
1236          30
1236          40
1237          50
9876          10
9876          20
9876          30
9876          40
9877          10
9877          40
9877          50
9878          10
9878          20
9878          30
9878          50

经过几个疯狂的子查询（太长无法在此处发布），我可以找到一个表格，显示每个供应商缺少的内容，然后我的想法是根据那些缺少的项目加入该表格，然后显示具有精致的供应商列表。

结果应该看起来像（逗号分隔与否无关紧要，更容易阅读）

vender_1 | product_id   |   missing_product_id   | vendor_2
1234       10,20,30,40          50                 9876,9878
1235        10,40            20,30,50              9878
1236        20,30,40           10,50               9877
1237        50              10,20,30,40            1234

或者

vender_1 | product_id   |   missing_product_id   | vendor_2
1234           10          
1234           20              
1234           30           
1234           40                                 
1234                              50                 9876
1234                              50                 9878

ETC...

标签： mysqlsqlmatchwhere-in

解决方案

你想要group by并且having：

SELECT v.vendor_id
FROM vendors v  -- Shouldn't this be called vendorProducts ?
WHERE v.product_id IN (10, 20, 30, 40, 50) 
GROUP BY v.vendor_id
HAVING COUNT(DISTINCT v.product_id) = 5;

对于多个供应商，您可以扩展上述逻辑。这个想法是将表格连接在一起以获得供应商对的列表以及他们一起拥有的所有产品。然后，执行与上述相同的逻辑：

SELECT v.vendor_id1, v.vendor_id2
FROM (SELECT DISTINCT v1.vendor_id as vendor_id1, v2.vendor_id as vendor_id2,
             (CASE WHEN n.n = 1 THEN v1.product_id ELSE v2.product_id END) as product_id
      FROM vendors v1 JOIN
           vendors v2
           ON v1.product_id <> v2.product_id AND
              v1.vendor_id < v2.vendor_id CROSS JOIN
           (SELECT 1 as n UNION ALL SELECT 2) n
      UNION ALL
      -- Then include the singletons, just in case
      SELECT v.vendor_id, NULL, v.product_id
      FROM vendors v
     ) v
WHERE v.product_id IN (10, 20, 30, 40, 50) 
GROUP BY v.vendor_id1, v.vendor_id2
HAVING COUNT(DISTINCT v.product_id) = 5;

您实际上可以在子查询中进行产品过滤——以提高查询效率。至于使这个更笼统，“5”是项目的数量。我不知道最终查询是如何构建的。

编辑二：

这是一个有大量数据的难题。如果您有很多产品和少数供应商，这是另一种可能效果更好的方法：

select v1.*, v2.*
from (select vendor_id,
             max(product_id = 1) as p1,
             max(product_id = 2) as p2,
             max(product_id = 3) as p3,
             max(product_id = 4) as p4,
             max(product_id = 5) as p5,
      from vendors
      where product_id in (1, 2, 3, 4, 5)
      group by vendor_id
     ) v1 join
     (select vendor_id,
             max(product_id = 1) as p1,
             max(product_id = 2) as p2,
             max(product_id = 3) as p3,
             max(product_id = 4) as p4,
             max(product_id = 5) as p5,
      from vendors
      where product_id in (1, 2, 3, 4, 5)
      group by vendor_id
     ) v2
     on (v1.p1 + v2.p1) > 0 and
        (v1.p2 + v2.p2) > 0 and
        (v1.p3 + v2.p3) > 0 and
        (v1.p4 + v2.p4) > 0 and
        (v1.p5 + v2.p5) > 0;

注意：如果一个供应商拥有所有产品，那么它将与其他所有供应商配对。

推荐阅读